CASE statement and NULL values

• winash

  SSChampion

  Points: 11446

  More actions

  August 7, 2002 at 5:17 am

  #79170

  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

  More actions

  August 7, 2002 at 5:29 am

  #434188

  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

  More actions

  August 7, 2002 at 5:36 am

  #434190

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

• NPeeters

  SSChampion

  Points: 12536

  More actions

  August 7, 2002 at 7:51 am

  #434208

  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

  More actions

  August 8, 2002 at 2:32 pm

  #434297

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

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