Case in where clause doesn't get validated

Question

Case in where clause doesn't get validated

halifaxdal

SSCoach

Points: 19741
More actions
September 26, 2012 at 8:48 am

#267247

Hello,
I want to add filter NextReviewDate to the query in the where clause, how come this doesn't work? It says something wrong at the first case
where d.SA_ID like case when @SA_ID <> 0 then @SA_ID else d.SA_ID end and d.doctype = 1
and
d.ciodivisionid like case when @DivisionID <> 0 then @DivisionID else d.ciodivisionid end
and
case When @Due = 'Over Due' then NextReviewDate < getDate()
case When @Due = 'Due In 30 Days' then NextReviewDate >= getDate() And NextReviewDate < getDate() + 30
case When @Due = 'Due In 60 Days' then NextReviewDate >= getDate() + 30 And NextReviewDate < getDate() + 60
case When @Due = 'Due In 90 Days' then NextReviewDate >= getDate() + 60 And NextReviewDate < getDate() + 90
case When @Due = 'Due In 180 Days' then NextReviewDate >= getDate() + 90 And NextReviewDate < getDate() + 180
else
@Due = @Due
end
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

a case can't due logic...it must return a single value; you are trying to use it more like an If statement to get NextReviewDate < getDate() for example.

i think it's more like this:

...

where d.SA_ID like case

when @SA_ID <> 0

then @SA_ID

else d.SA_ID

end

and d.doctype = 1

and d.ciodivisionid like case

when @DivisionID <> 0

then @DivisionID

else d.ciodivisionid

end

and NextReviewDate BETWEEN case

When @Due = 'Over Due'

then 0

ELSE getDate()

END

AND case

When @Due = 'Over Due'

then getDate()

When @Due = 'Due In 30 Days'

THEN getDate() + 30

When @Due = 'Due In 60 Days'

THEN getDate() + 60

When @Due = 'Due In 90 Days'

THEN getDate() + 90

When @Due = 'Due In 180 Days'

THEN getDate() + 180

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Something similar to Lowell's but I believe he missed something.

AND NextReviewDate >= CASE WHEN @Due = 'Over Due' THEN '19000101'

WHEN @Due = 'Due In 30 Days' THEN GETDATE()

WHEN @Due = 'Due In 60 Days' THEN DATEADD( dd, 30, GETDATE())

WHEN @Due = 'Due In 90 Days' THEN DATEADD( dd, 60, GETDATE())

WHEN @Due = 'Due In 180 Days' THEN DATEADD( dd, 90, GETDATE())

ELSE '19000101' END

AND NextReviewDate < CASE WHEN @Due = 'Over Due' THEN GETDATE()

WHEN @Due = 'Due In 30 Days' THEN DATEADD( dd, 30, GETDATE())

WHEN @Due = 'Due In 60 Days' THEN DATEADD( dd, 60, GETDATE())

WHEN @Due = 'Due In 90 Days' THEN DATEADD( dd, 90, GETDATE())

WHEN @Due = 'Due In 180 Days' THEN DATEADD( dd, 180, GETDATE())

ELSE '29991231' END

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

(CROSS) APPLY is ideal for this:

CROSS APPLY (

SELECT DueStatus =

case

When NextReviewDate < getDate() then 'Over Due'

When NextReviewDate >= getDate() And NextReviewDate < getDate() + 30 then 'Due In 30 Days'

When NextReviewDate >= getDate() + 30 And NextReviewDate < getDate() + 60 then 'Due In 60 Days'

When NextReviewDate >= getDate() + 60 And NextReviewDate < getDate() + 90 then 'Due In 90 Days'

When NextReviewDate >= getDate() + 90 And NextReviewDate < getDate() + 180 then 'Due In 180 Days'

ELSE NULL END

) x

where d.SA_ID like case when @SA_ID <> 0 then @SA_ID else d.SA_ID end and d.doctype = 1

and d.ciodivisionid like case when @DivisionID <> 0 then @DivisionID else d.ciodivisionid end

and x.dueStatus = @Due

^{“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

halifaxdal SSCoach Points: 19741 More actions · Answer 4

ChrisM@Work (9/26/2012)
(CROSS) APPLY is ideal for this:
CROSS APPLY (
SELECT DueStatus =
case
When NextReviewDate < getDate() then 'Over Due'
When NextReviewDate >= getDate() And NextReviewDate < getDate() + 30 then 'Due In 30 Days'
When NextReviewDate >= getDate() + 30 And NextReviewDate < getDate() + 60 then 'Due In 60 Days'
When NextReviewDate >= getDate() + 60 And NextReviewDate < getDate() + 90 then 'Due In 90 Days'
When NextReviewDate >= getDate() + 90 And NextReviewDate < getDate() + 180 then 'Due In 180 Days'
ELSE NULL END
) x
where d.SA_ID like case when @SA_ID <> 0 then @SA_ID else d.SA_ID end and d.doctype = 1
and d.ciodivisionid like case when @DivisionID <> 0 then @DivisionID else d.ciodivisionid end
and x.dueStatus = @Due

Thanks for all replies, I like this because it is closest to my original, it gets validated however, running it pops error:

Incorrect syntax near the keyword 'Over'.

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

Post the whole query, mate...

^{“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

halifaxdal SSCoach Points: 19741 More actions · Answer 6

My bad! I hit another sp, it works, amazingly like what I expect. Thanks lots.

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

halifaxdal (9/26/2012)
My bad! I hit another sp, it works, amazingly like what I expect. Thanks lots.

Any time. There's a great set of articles by Paul White covering APPLY, links in my sig.

^{“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