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)

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