Oracle Query - Newbie Question

[ChiTownDiva]

Okay I have this Oracle query that I need to incorporate into a report:

SELECT INITCAP(LOWER(CONCAT(P.PERS_LAST_NAME||', ',P.PERS_FIRST_NAME))) AS "Student Name",
P.LOCAL_PERS_ID AS "RC Code/HRC", P.PERS_COST_CD AS "Cost Code",
P.PERS_LOGIN_ID AS "CUID/UID", P.PERS_SSN AS "SSN", P.JOB_TITLE AS "Job Title Code",
P.COMPANY AS "Company", P.ADDRESS_STATE AS "State", P.MGT_LEVEL AS "Mgt Level",
NVL((INITCAP(LOWER(CONCAT(SUPV.PERS_LAST_NAME||', ', SUPV.PERS_FIRST_NAME)))), 'NONE') AS
"Supervisor", SUPV.PERS_PHONE_NUM AS "Sup Phone Num", SUPV.PERS_LOGIN_ID AS "Sup CUID",
TO_CHAR(P.JOB_TITLE_EFFECTIVE_DT, 'MM/DD/YYYY') AS "JTC", P.PERSON_STATUS AS "Status"
FROM
TEDSCBM.PERSON P, TEDSCBM.PERSON SUPV
WHERE
P.PERS_SUPER_ID = SUPV.PERS_ID(+)
AND
P.PERS_ID NOT IN
(SELECT PC.PERS_ID
FROM
TEDSCBM.PERSON_COURSE PC
WHERE
PC.LOCAL_CRSE_CD LIKE UPPER('&Course')
AND
( PC.STATUS_CD = 'P' OR
PC.STATUS_CD = 'L' OR
PC.STATUS_CD = 'X' OR
PC.STATUS_CD = 'CS' OR
PC.STATUS_CD = 'CX' OR
PC.STATUS_CD = 'EQ' OR
PC.STATUS_CD = 'CM' OR
PC.STATUS_CD = 'CE' OR
PC.STATUS_CD = 'CC' OR
PC.STATUS_CD = 'PP' OR
PC.STATUS_CD = 'PM' OR
PC.STATUS_CD = 'PD') AND
PC.COMPLETION_IND = 'Y' AND
(PC.END_DT BETWEEN TO_DATE('&Start_Date', 'MM/DD/YYYY') AND
TO_DATE('&End_Date', 'MM/DD/YYYY')))
AND
P.LOCAL_PERS_ID LIKE UPPER('&RC_HRC') AND
P.COMPANY LIKE '&Company' AND
P.ADDRESS_STATE LIKE UPPER('&State') AND
(P.PERSON_STATUS = 'A' OR
P.PERSON_STATUS = 'L') AND
P.MGT_LEVEL LIKE '&Mgt_Level' AND
P.PERS_COST_CD LIKE UPPER('&Cost_Code') AND
P.JOB_TITLE LIKE '&Job_Title'

I got the datasource to work, and it pulled the fields of the query into the Field Explorer. But when I create parameters and run the report nothing comes back. Why is that?

ChiTownDivaus

 

[synapsevampire]

Did you update the record selection criteria to use the parameters?

I would load the tables and emulate this query in CR, or perhaps use the CR SQL Designer (which I caution against).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ChiTownDiva]

Thanks synapsevampire...

Yes, I updated the record selection to use the parameters.

CR won't run a subquery, particulaly if has a NOT IN or NOT EXIST. It comes back blank...

ChiTownDivaus

 

[Naith]

Hey ChiTownDivaus,

As you know negative clauses don't translate to Crystal very well. If you create all your parameters except {?Course}, does Crystal successfully execute?

I would suggest replacing each of your Oracle parameters with a Crystal parameter, one at a time, so you can isolate when you start getting blank data. I would assume it's occuring with the parameter in your NOT IN statement.

Naith