A Development Question

[ChiTownDiva]

I'm running a query in Oracle that we need converted into a stored procedure:

SELECT INITCAP(LOWER(CONCAT(P.PERS_LAST_NAME||', ',P.PERS_FIRST_NAME))) AS "Student Name", P.LOCAL_PERS_ID AS "RC Code/HR", P.PERS_COST_CD AS "TU Code",
P.PERS_LOGIN_ID AS "CUID/UID", P.PERS_SSN AS "SSN", P.JOB_TITLE AS "Job Title", 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", 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 'CN181D-02' OR
PC.LOCAL_CRSE_CD LIKE 'CN181B-02' OR
PC.LOCAL_CRSE_CD LIKE 'CN181J-02')
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
TO_DATE('10/31/2002', 'MM/DD/YYYY')>=PC.END_DT AND
TO_DATE('7/01/2002', 'MM/DD/YYYY')<=PC.START_DT)
AND
P.LOCAL_PERS_ID LIKE '&RC_Code' AND
P.COMPANY LIKE '&Company' AND
P.ADDRESS_STATE LIKE '&State' AND
(P.PERSON_STATUS = 'A' OR
P.PERSON_STATUS = 'L') AND
P.MGT_LEVEL LIKE '&Mgt_Level' AND
P.PERS_COST_CD LIKE '&TU_Code' AND
P.JOB_TITLE LIKE '&Job_Title'

One of the developers asked the question &quot;how is Crystal Reports going to pass the parameters to the stored procedure and how is crystal going to call the results back?&quot;

I understand that this has to be done as a ref cursor, but how does crystal return records from a ref cursor?

Thanks.

ChiTownDivaus

 

[synapsevampire]

Crystal automatically detects parameters in a Oracle SP, and places them in the report.

The data will be returned as a single set of rows (like a table).

Just make sure that you follow the CR requirements for SP's:

http://support.crystaldecisions.com/library/kbase/articles/c2001157.asp

They are dependent upon the connection type, version of Crystal, version of Oracle, and for supported data types only.

Though the performance isn't as good, you could also create a View from this SQL and apply a Where clause to it according to your requirements. This offers more flexibility and reusability, but it degrades performance.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com