Steven Duncan Steven Duncan - 5 months ago 38
SQL Question

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.

Answer

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