CODEWITHSUNDEEP
sqlsql-servercase
user3115933
user3115933

Reputation: 4453

Why is this CASE statement giving NULLS instead of the ELSE condition therein?

I am running SQL Server 2014 and I have the following T-SQL query:

SELECT   a.ResaID, 
            b.[Market Final],
          (CASE 
              WHEN  b.[Market FINAL] = 'Overbooking' AND b.[TAPRofileID3] IS NOT NULL   
              THEN   b.[Overbooking Market Classification]
                ELSE  b.[Market FINAL]
            END) AS 'Market Re-Classified'

When I run this Query I get the following output:

ResaID    Market Final     Market Re-Classified
 102      Overbooking       NULL
 251      Overbooking       France
 680      Overbooking       Germany

I can't understand why I am having a NULL for ResaID 102. I was expecting "Overbooking" as its output since my ELSE condition states that if TAProfileID3 is NULL, then it should return the Market Final.

How do I re-write my code to maintain this logic?

Upvotes: 0

Views: 81

Answers (3)

HLGEM
HLGEM

Reputation: 96600

I have been caught before where somehow the word Null got put into the field and it was not NULL at all in terms of checks for nulls. Check for this on your record by querying:

select * from tableb where resaid = 102 and [market re-classified] = 'null'

Upvotes: 0

Lisa F.
Lisa F.

Reputation: 11

More than likely b.[Overbooking Market Classification] is null. I had this issue a bit ago, just add it in the same way you did the TAPR and should be good. You're using 3 fields and only checking 2 for nulls.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

The 

THEN   b.[Overbooking Market Classification]

or WHEN b.[Market FINAL] = 'Overbooking' AND b.[TAPRofileID3] IS NOT NULL

fail and b.[Market FINAL]

could contain null

or

and for these column you can use 

 coalesce(youcolumn, 'your_default_value');

Upvotes: 1

Related Questions