CASE statement

Question

Post reply

CASE statement

peacesells

SSCrazy

Points: 2882
More actions
April 8, 2013 at 7:28 pm

#292123

WHERE SS.[Key]='A'
AND SO.[type]='X_P'
AND vu.[Status]=1
AND 1= CASE WHEN @CreateDateDatTime > vu.LastRunDate
THEN 1ELSE
---Secondary filter
(
L.create_date>=ISNULL(vu.LastRunDate,'01/01/1900')
OR L.modify_date>=ISNULL(vu.LastRunDate,'01/01/1900')
)
What i am trying to do here is to use the case in the filter, if @CreateDateDatTime>vu.LastRunDate THEN don't apply the secondary filter (select everything that satisfies other filters) or else apply the secondary filter as well. Can't put it together. any help?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mansfield SSCrazy Points: 2396 More actions · Answer 1

The CASE statement is used for conditional substitution of values, not formulae.

Perhaps the logic can be refactored into the WHERE clause?

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

peacesells (4/8/2013)
WHERE SS.[Key]='A' AND SO.[type]='X_P' AND vu.[Status]=1 AND 1= CASE WHEN @CreateDateDatTime > vu.LastRunDate THEN 1ELSE ---Secondary filter ( L.create_date>=ISNULL(vu.LastRunDate,'01/01/1900') OR L.modify_date>=ISNULL(vu.LastRunDate,'01/01/1900')
)
What i am trying to do here is to use the case in the filter, if @CreateDateDatTime>vu.LastRunDate THEN don't apply the secondary filter (select everything that satisfies other filters) or else apply the secondary filter as well. Can't put it together. any help?

It looks like you did not show us the rest of your CASE expression so it will be difficult to say what might be wrong with it. It should have been terminated with an END. Can you please post the entire WHERE clause?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Use APPLY to construct a value which can then be used in the WHERE clause. This code chunk may well be incorrect - as Orlando's pointed out, some of the CASE is missing - but I'm sure you will get the general idea. Once you've tested and verified the expression, it can be subbed into the WHERE clause, but for the tiny performance hit it's often better to keep the APPLY to make the code more readable:

CROSS APPLY (

SELECT SecondaryFilter = CASE

WHEN L.create_date >= ISNULL(vu.LastRunDate,'01/01/1900')

OR L.modify_date >= ISNULL(vu.LastRunDate,'01/01/1900') THEN 1 ELSE NULL END

) x

WHERE SS.[Key]='A'

AND SO.[type]='X_P'

AND vu.[Status]=1

AND @CreateDateDatTime > vu.LastRunDate OR x.SecondaryFilter = 1

-- SARGable equivalent

CROSS APPLY (

SELECT SecondaryFilter = CASE

WHEN vu.LastRunDate IS NULL

OR L.create_date >= vu.LastRunDate

OR L.modify_date >= vu.LastRunDate THEN 1

ELSE NULL END

) x

WHERE SS.[Key]='A'

AND SO.[type]='X_P'

AND vu.[Status]=1

AND @CreateDateDatTime > vu.LastRunDate OR x.SecondaryFilter = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden