We help IT Professionals succeed at work.
Get Started

Adding second check to case statement give error.

MikeM670
MikeM670 asked
on
57 Views
Last Modified: 2019-12-16
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.
Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE