CODEWITHSUNDEEP
sqlt-sqlcase
Myurathan Kajendran
Myurathan Kajendran

Reputation: 677

Using CASE statement with isnull and else

I have a column Color which contains Black, Red, NULL, WW, RR.

I want a column which has

if color black then 'B'
if color red then 'r'
if color is Null then 'Empty'
for all other entries 'n/a'

I'm using the following, but it throws an error:

SELECT Name,
        CASE color
            WHEN 'black' THEN 'b'
            WHEN 'red' THEN 'r'
            WHEN ISNULL(color, 'empty')
            else 'n/a'
           END AS Color_code
FROM SalesLT.Product;

Upvotes: 7

Views: 57340

Answers (3)

Wild developer
Wild developer

Reputation: 1

Try this:

        CASE color
            WHEN 'black' THEN 'b'
            WHEN 'red' THEN 'r'
            else 'n/a'
            END* ISNULL(color, 'empty') AS Color_code
        FROM SalesLT.Product; ```

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

You can use either CASE structure to do this, but you can't call a function if you use the CASE fieldname WHEN approach, so you can either use CASE WHEN fieldname condition:

SELECT Name,
        CASE WHEN color = 'black' THEN 'b'
            WHEN color = 'red' THEN 'r'
            WHEN color IS NULL THEN  'empty'
            else 'n/a'
           END AS Color_code
FROM SalesLT.Product;

OR:

SELECT Name,
        CASE color
            WHEN 'black' THEN 'b'
            WHEN 'red' THEN 'r'
            WHEN NULL THEN 'empty'
            else 'n/a'
           END AS Color_code
FROM SalesLT.Product;

Upvotes: 13

ScaisEdge
ScaisEdge

Reputation: 133360

You could try this way 

  SELECT Name,
          CASE 
              WHEN color = 'black' THEN 'b'
              WHEN color = 'red' THEN 'r'
              WHEN color is null  THEN  'empty'
                ELSE 'n/a'
          END AS Color_code
  FROM SalesLT.Product;

Upvotes: 5

Related Questions