Avatar of MikeM670
MikeM670
 asked on

Adding second check to case statement give error.

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

Open in new window


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

Open in new window


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

Open in new window


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.
SQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
MikeM670

ASKER
Scott,

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
Scott Pletcher

You're welcome.  A lot of times a quick example is far better than a long-winded description of the solution.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck