# CASE statement and NULL values

• winash

SSChampion

Points: 11446

Hi,

Is there a problem in checking NULL values in a CASE statement...the following example illustrates my problem -

Create table #Test1(F1 Int,F2 Int)

/*

Insert #Test1 Values(NULL,1)

Insert #Test1 Values(1,2)

Insert #Test1 Values(2,3)

Insert #Test1 Values(3,NULL)

*/

Now assume I want to get the values from the table in such a way that if F1 is null then it should display F2 and if F2 is null then it should display F1...If I use a case statement as follows :

SELECT CASE F1 WHEN NULL THEN F2 ELSE F1 END F11,CASE F2 WHEN NULL THEN F1 ELSE F2 END F22

FROM #Test1

it gives me an incorrect result and if I use the following statement I get the correct values

SELECT CASE isNull(F1,-10) WHEN -10 THEN F2 ELSE F1 END F11,CASE isNull(F2,-10) WHEN -10

THEN F1 ELSE F2 END F22

FROM #Test1

Drop table #Test1

Is this a restriction with NULL values or is there some problem in the syntax??

• Andy Jones, DBA

Hall of Fame

Points: 3641

Try this: -

SELECT CASE WHEN F1 is NULL THEN F2 ELSE F1 END F11,CASE WHEN F2 is NULL THEN F1 ELSE F2 END F22

FROM #Test1

Regards,

Andy Jones

.

• winash

SSChampion

Points: 11446

This works fine......thanks.........

• NPeeters

SSChampion

Points: 12193

You can also just use the IsNull function as in :

SELECT IsNull(F1,F2) F11, IsNull(F2,F1) F22

FROM #Test1

• winash

SSChampion

Points: 11446

hmmmmm...that seems so obvious - now that I think of it.... 😉

I have to learn to use this thing called the brain more often.....

Viewing 5 posts - 1 through 5 (of 5 total)