CODEWITHSUNDEEP
sqlsql-server
Amaan Khan
Amaan Khan

Reputation: 89

I want to return a default value as NULL for some condition in SQL Server

I am running the below SQL query in SQL Server where I need to return a default value as ’NULL’ when a condition is not met. The result should be displayed as NULL for some records which doesn’t meet the criteria but I am getting a blank space in-place of NULL.Can someone please help?

There are few rows in my table which doesn’t meet the criteria so I should be seeing NULL in my result.

SELECT NAME,
CASE 
     WHEN AGE >=18 AND SEX='M' THEN 'Adult Male' 
     WHEN AGE>18 AND SEX='M' 'Non-Adult Male'
     WHEN AGE >=18  AND SEX='F' THEN 'Adult Female'
     WHEN AGE<18 AND TITLE='F' THEN 'Non-Adult Female'
     ELSE NULL
END AS AGE_SEX,
ADDRESS, 
SALARY
FROM PERSONALS;

Upvotes: 0

Views: 1449

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Your logic is not correct. Here is a way to fix it, taking into account that case statements cascade -- that is, the logic chooses the first match:

SELECT NAME,
       (CASE WHEN AGE >= 18 AND SEX = 'M' THEN 'Adult Male'
             WHEN AGE < 18 AND SEX = 'M' THEN 'Non-Adult Male'
             WHEN AGE >= 18 AND SEX = 'F' THEN 'Adult Female'
             WHEN AGE < 18 AND SEX = 'F' THEN 'Non-Adult Female'
        END) AS AGE_SEX

Note: The ELSE NULL is redundant, because the CASE returns NULL when no conditions match.

Upvotes: 2

Related Questions