troubleshooting Question

Adding second check to case statement give error.

Avatar of MikeM670
MikeM670 asked on
SQL
3 Comments1 Solution59 ViewsLast Modified:
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

It now contains errors.

Just a brief explanation on some of the code.

-- [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)]

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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros