Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Suppress Future Type Parameter

Status
Not open for further replies.

cocopud

Technical User
Jan 8, 2002
139
0
0
US
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;

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top