Unanswered: A Correlated Sub-Query, Crystal Reports,Command Object, Parameters, and SubReports
I am trying to use a more advanced feature of using the Command Object in Crystal Reports and Sub-Reports. My main report has a parameter that is called ?PM-ALCA_CRIM_ALL.CASEID. I am trying to link it to a sub-report within Crystal Reports, which does not use a table or a view, but instead issues an SQL Statement to pull the resultset in the Sub-Report. I need to use this statement or the Command Object in my sub-report as I need a complex statement to get the data I need. I have given up on Arrays and Formulas within my sub-report as these features are very limitted in Crystal Reports. I am trying to dynamically create my SQL statement where by referring to the PM-ALCA_CRIM_ALL.CASEID paramter. See my code below.
I have tried ?PM-ALCA_CRIM_ALL.CASEID and #PM-ALCA_CRIM_ALL.CASEID# and every other way of doing this dynamically.
Can someone please help me out here. I know how to link parameters in sub-reports, but only while using Views and Tables as my datasources. I cannot get it to work if I am using a statement to get my Resultset.
SELECT DISTINCT PARTICIPANTS1.LAST_NAME, PARTICIPANTS1.FIRST_NAME, COUNTS1.CHARGE, COUNTS1.COUNTID,
COUNTS1.CASEID,INST_FILING_DATE, SENTENCES1.SENT_DATE FROM LIONS.L3D2_CR_INST_PARTS_COUNTS COUNTS1,
lions.l3d_participant PARTICIPANTS1, LIONS.V_GS_SENTENCE SENTENCES1
WHERE INST_FILING_DATE = (SELECT MAX(INST_FILING_DATE) FROM
WHERE COUNTS1.CASEID = COUNTS2.CASEID AND COUNTS1.PARTID = COUNTS2.PARTID)
AND PARTICIPANTS1.CASEID = COUNTS1.CASEID
AND PARTICIPANTS1.ID = COUNTS1.PARTID
AND COUNTS1.PARTID = SENTENCES1.PARTID (+)
AND COUNTS1.CASEID = SENTENCES1.CASEID (+)
AND COUNTS1.CASEID = ?Pm-ALCA_CRIM_ALL.CASEID
ORDER BY LAST_NAME, COUNTID, CHARGE