CODEWITHSUNDEEP
sqlwhere-clausecase-statement
Steven Duncan
Steven Duncan

Reputation: 39

SQL Case Statement: Inside Where Clause

I have read some other Q&A about case statements inside the 'WHERE' clause, but I cannot truly understand how to use it. I will post below a snippet of the code. I believe I am ignorant of a fundamental principle concerning how to use a case statement, and this is why the code will not compile/run. I appreciate any help.

where i.status IN ('CR','L','O')
and i.FGCs > 0
and i.LastShpd > CAST(CONVERT(CHAR(11),DATEADD(DAY,-180,GETDATE()),113) AS datetime) 
and (Case
    When n.OnOrder IN ('0', '')
    Then i.OnOrder = 0 or i.LastShpd < CAST(CONVERT(CHAR(11),DATEADD(DAY,-21,GETDATE()),113) AS datetime)))
    End)

Order by i.LastShpd desc

To explain what I have above, I already got the appropriate 'SELECT' and 'FROM' statement. Now I am filtering the results based on those the shown variables (ecx LastShpd). What I wanted the case statement to do was: When n.OnOrder = 0, I want to keep only the rows where i.OnOrder = 0 or if i.LastShpd has been greater than 21 days.

Upvotes: 0

Views: 95

Answers (2)

Joel
Joel

Reputation: 2257

When using a CASE within a WHERE clause, you still need to have both sides of the operation defined (i.e. [CASE CONDITION] = [SOMETHING]). This can get tricky depending on what you want to do, but the easiest thing is to have your case statement execute as a true/false type of condition so that you end up with [CASE] = 1 or [CASE] = 0.

In your case (accidental pun!):

where i.status IN ('CR','L','O')
and i.FGCs > 0
and i.LastShpd > CAST(CONVERT(CHAR(11),DATEADD(DAY,-180,GETDATE()),113) AS datetime) 
and (Case
    When n.OnOrder IN ('0', '') AND (i.OnOrder = 0 or i.LastShpd < CAST(CONVERT(CHAR(11),DATEADD(DAY,-21,GETDATE()),113) AS datetime)) 
    THEN 1
    ELSE 0
    End) = 1

Of course as another answer has pointed out, a case is not really necessary in this particular instance. However, if you had more complex conditions this could be helpful.

Upvotes: 1

vercelli
vercelli

Reputation: 4757

I don't think you need a Case for this:

where i.status IN ('CR','L','O')
and i.FGCs > 0
and i.LastShpd > CAST(CONVERT(CHAR(11),DATEADD(DAY,-180,GETDATE()),113) AS datetime) 
and (
     (n.OnOrder IN ('0', '') and i.OnOrder = 0) 
 or i.LastShpd < CAST(CONVERT(CHAR(11),DATEADD(DAY,-21,GETDATE()),113) AS datetime)
    )

Re-reading your question maybe is this other way:

where i.status IN ('CR','L','O')
and i.FGCs > 0
and i.LastShpd > CAST(CONVERT(CHAR(11),DATEADD(DAY,-180,GETDATE()),113) AS datetime) 
and (
     n.OnOrder Not IN ('0', '') 
    or i.OnOrder = 0 
    or i.LastShpd < CAST(CONVERT(CHAR(11),DATEADD(DAY,-21,GETDATE()),113) AS datetime)
    )

Upvotes: 3

Related Questions