I know that some of this query would best be done with the application layer instead of in sql but I have to work this way.
The query contains code for a checkbox. If the checkbox is selected and the criteria for the date range is valid it will return records based on them. If the checkbox is not selected the case statement will fall to the next option,
-- [PSIMSQQ From_Date(Format:mm/dd/yyyy)] 00:00:00-- [PSIMSQQ To_Date(Format:mm/dd/yyyy)] 23:59:59-- [PSIMSQQ Checkbox(Format: By Arrest Date)]SELECT CASE WHEN '[+PSIMSQQ Checkbox(Format: By Arrest Date)]' = 'on' AND (cpc.arrestdate >= '[+PSIMSQQ From_Date(Format:mm/dd/yyyy)] 00:00:00' or cpc.arrestdate <= '[+PSIMSQQ To_Date(Format:mm/dd/yyyy)] 23:59:59') THEN cpc.arrestdate ELSE cm.occurredstartdate END as Date, *FROM casemaster cm, CasePersonCharges cpc
The above code shows no errors in manager but if I change the code to
-- [PSIMSQQ From_Date(Format:mm/dd/yyyy)] 00:00:00-- [PSIMSQQ To_Date(Format:mm/dd/yyyy)] 23:59:59-- [PSIMSQQ Checkbox(Format: By Arrest Date)]SELECT CASE WHEN '[+PSIMSQQ Checkbox(Format: By Arrest Date)]' = 'on' AND (cpc.arrestdate >= '[+PSIMSQQ From_Date(Format:mm/dd/yyyy)] 00:00:00' or cpc.arrestdate <= '[+PSIMSQQ To_Date(Format:mm/dd/yyyy)] 23:59:59') THEN cpc.arrestdate ELSE IF cm.occurredstartdate >= '[+PSIMSQQ From_Date(Format:mm/dd/yyyy)] 00:00:00' or cm.occurredstartdate <= '[+PSIMSQQ To_Date(Format:mm/dd/yyyy)] 23:59:59' THEN cm.occurredstartdate ELSE '' END as Date, *FROM casemaster cm, CasePersonCharges cpc
is used by the application to force the display of the parameters in this order.
Where you see '[+PSIMSQQ Checkbox(Format: By Arrest Date)]' (the + in front of the PSIMSQQ) tells the application program to use the value already entered previously.
That did the trick. I can go on and modify this to add the extra information I need returned. Funny how your example was obvious when I saw it.
Thanks
MIke