November 15, 2007 at 7:56 am
Hello there,
I'm trying to figure this one out... Suppose I have a case statement in my query, as in,
SELECT A,
CASE
WHEN b IS NULL THEN 0
WHEN b = 'A' THEN 1
WHEN b = 'B' THEN 2
ELSE 3
END AS TEST
FROM TABLE_A WHERE TEST = 2
I don't know if you see what I mean. Sometimes I use the case statement to compute a status to return based on a date for example compared to GETDATE() and I would like to filter my results with the case instead of having to rewrite the CASE condition in the WHERE.
Is that possible or do I have to use a function as my only choice?
Thanks
Greg
November 15, 2007 at 9:18 am
Blimey! That would sure confuse the hell out of anybody trying to maintain it! Here's a way of doing it which is very close, but which we've all seen and understand...
select * from (
SELECT A,
CASE
WHEN b IS NULL THEN 0
WHEN b = 'A' THEN 1
WHEN b = 'B' THEN 2
ELSE 3
END AS TEST
FROM TABLE_A ) t
WHERE TEST = 2
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
November 15, 2007 at 12:58 pm
Thanks a lot, i was hoping for a nicer way to do it, but this could do for simple queries
November 16, 2007 at 12:47 am
My apologies if I sound dense, but I'd like to know the utility gained from taking the trouble to define an elaborate CASE clause and then filtering records on only one of those CASE conditions. Wouldn't it make more sense to use:
[Code]SELECT A, 2 as TEST
FROM TABLE_A
WHERE b = 'B'[/Code]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply