Nested case Statment
-
This is what I am trying, but I am getting an error message:
Select case when test1 is null then test2 else
(case when test2 is null then test3 else
(case when test3 is null then Test4 end)) as sample1
I can do this in Excel with a nested IF, I know that but I need to do this in SQL.
If test 1 has a value, then I want to grab the value in the test1 field, if not then I need to look in test2, and so on, if there is nothing in any of the fields, then I will leave it as a null. I am close, but got frustated and then started down another rat hole, I know that this is simple, what am I missing?
Richard
-
You might want to rethink the table design if at all possible... there should be a new table containing the tests instead of 5-10-20 columns???
You were just missing 2 end statements.
Select
case when test1 is null then test2 else
(case when test2 is null then test3 else
(case when test3 is null then Test4 end) end) end as sample1
-
Richard
COALESCE is more concise when you're testing for nulls:
SELECT COALESCE (test1, test2, test3, test4) AS sample1
John
-
Good point... I forgot that you pretty much had unlimited variables with that function
. -
Richard,
Each instance of CASE must have an END... You have 3 instances of CASE but only 1 END. That would be the problem.
However, the other guys are right... in this particular case (no pun intended), COALESCE is the way to go...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I have to remember that one
... " particular case (no pun intended)"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply