I am using Crystal Report XI with a Oracle database. I have a report that currently has a startdate, enddate and a type parameter. The Type can be an addition, deletion or modification. Currently when the report is run, it shows modifications that have a future startdate. I need to add a parameter that if “Yes” is selected, future modifications are shown and if “No” is selected, it does not show the future modifications.
I would like to do this in the stored procedure, but not sure how to set up the parameter. Seems it should be pretty easy, but I am not really good with the sql stored procedure stuff and not sure if I have to consider the parameters that are already there. Below is what is already in the stored procedure.
Can anyone give me guidance as to the best way to create the new parameter would be helpful, I haven’t ever created a Y/N parameter, so not sure if I would use a Boolean or something else. Thanks
IF LENGTH(pTYPE) > 0 THEN
vSQL := vSQL || ' AND CCL.TYPE = ''' || pTYPE || '''';
ELSE vSQL := vSQL || ' AND CCL.TYPE IN (''A'', ''D'', ''M'') ';
END IF;
vSQL := vSQL || ' JOIN PEOPLE P ON L.MODIFIED_BY = P.ID ';
vWhere := q'< WHERE
(L. STATUS = 'O' AND (
CCL.TYPE = 'M' AND L.MODIFIED_DATETIME < TO_DATE('>' || vStartDate || q'< 00:00:00', 'MM/DD/YYYY hh24:mi:ss')) OR
(L.MODIFIED_DATETIME >= TO_DATE('>' || vStartDate || q'< 00:00:00', 'MM/DD/YYYY hh24:mi:ss') AND
L.MODIFIED_DATETIME <= TO_DATE('>' || vEndDate || q'< 23:59:59', 'MM/DD/YYYY hh24:mi:ss'))
OR
(L.START_DATETIME >= TO_DATE('>' || vStartDate || q'< 00:00:00', 'MM/DD/YYYY hh24:mi:ss') AND
L.START_DATETIME <= TO_DATE('>' || vEndDate || q'< 23:59:59', 'MM/DD/YYYY hh24:mi:ss')))
>';
vWhere := vWhere || q'< AND ((CCL.TYPE = 'LIR' AND TRIM(L.INTERRUPT_CONDITION) IN ('FL','RS','RE')) OR
( F.FAC_REPORT_FLAG IN ('M', 'N', 'R', 'S') AND CCL.TYPE IN ('A', 'D'))) >';
vSQL := vSQL || vWhere;
vSort := ' ORDER BY L.START_DATETIME ';
vSQL := vSQL || vSort;
I would like to do this in the stored procedure, but not sure how to set up the parameter. Seems it should be pretty easy, but I am not really good with the sql stored procedure stuff and not sure if I have to consider the parameters that are already there. Below is what is already in the stored procedure.
Can anyone give me guidance as to the best way to create the new parameter would be helpful, I haven’t ever created a Y/N parameter, so not sure if I would use a Boolean or something else. Thanks
IF LENGTH(pTYPE) > 0 THEN
vSQL := vSQL || ' AND CCL.TYPE = ''' || pTYPE || '''';
ELSE vSQL := vSQL || ' AND CCL.TYPE IN (''A'', ''D'', ''M'') ';
END IF;
vSQL := vSQL || ' JOIN PEOPLE P ON L.MODIFIED_BY = P.ID ';
vWhere := q'< WHERE
(L. STATUS = 'O' AND (
CCL.TYPE = 'M' AND L.MODIFIED_DATETIME < TO_DATE('>' || vStartDate || q'< 00:00:00', 'MM/DD/YYYY hh24:mi:ss')) OR
(L.MODIFIED_DATETIME >= TO_DATE('>' || vStartDate || q'< 00:00:00', 'MM/DD/YYYY hh24:mi:ss') AND
L.MODIFIED_DATETIME <= TO_DATE('>' || vEndDate || q'< 23:59:59', 'MM/DD/YYYY hh24:mi:ss'))
OR
(L.START_DATETIME >= TO_DATE('>' || vStartDate || q'< 00:00:00', 'MM/DD/YYYY hh24:mi:ss') AND
L.START_DATETIME <= TO_DATE('>' || vEndDate || q'< 23:59:59', 'MM/DD/YYYY hh24:mi:ss')))
>';
vWhere := vWhere || q'< AND ((CCL.TYPE = 'LIR' AND TRIM(L.INTERRUPT_CONDITION) IN ('FL','RS','RE')) OR
( F.FAC_REPORT_FLAG IN ('M', 'N', 'R', 'S') AND CCL.TYPE IN ('A', 'D'))) >';
vSQL := vSQL || vWhere;
vSort := ' ORDER BY L.START_DATETIME ';
vSQL := vSQL || vSort;