asked on

Nest CASE statements in SELECT statement

Hi Experts,

I have tow fields 'Product' and 'Type' that could have NULL values. So if Product is 'NULL' then it equals 'No_Product' or if 'Type' is NULL then it equals 'No_Type'. It can be on same row or only once of them on a row etc. Basically anytime either field is NULL then change it. I got the first one working no problem. Now I can't get the second one nested.

Works Great:

Select Currency,Year_of_Extract AS Years,Customer,Entity,Product,Scenario,Type,
VIEW1 AS 'View',Month_of_Extract AS Period,Account,Value as Data,

CASE
WHEN Product = '' THEN 'No_Produtct'
END

How do I add:

CASE
WHEN Type = '' THEN 'No_Type'
END

I tried all kinds of variations and I'm missing key concept.

Thanks in advance

elwayisgod

ASKER

I'm going down this path to no avail:

Currency,Year_of_Extract AS Years,Customer,Entity,Product,Scenario,Type,
VIEW1 AS 'View',Month_of_Extract AS Period,Account,Value as Data,

CASE
WHEN Product = '' THEN 'No_Product'
ELSE Product

WHEN Type = '' THEN 'No_Type'
ELSE Type
END

elwayisgod

ASKER

And 'IS NULL' is not working but the above is for some reason.

elwayisgod

ASKER

This works, but it puts it in two extra fields on the end both labeled 'Dummy'. It can't just change the original field?

Currency,Year_of_Extract AS Years,Customer,Entity,Product,Scenario,Type,
VIEW1 AS 'View',Month_of_Extract AS Period,Account,Value as Data,

CASE Product
WHEN '' THEN 'No_Product'
ELSE Product
END,
CASE Type
WHEN '' THEN 'No_Type'
ELSE Type
END

Scott Pletcher

CASE Product
WHEN '' THEN 'No_Product'
ELSE Product
END AS Product,
CASE Type
WHEN '' THEN 'No_Type'
ELSE Type
END AS Type

ste5an

You wrote about NULL, but you use an empty string in your sample. These are different things.

I also would tend to use ISNULL/NULLIF instead:

-- If it's NULL or a value.
ISNULL(Product, 'No_Product') AS Product,
ISNULL(Type, 'No_Type') AS Type,

-- If it's NULL/EMPTY STRING or a value.
ISNULL(NULLIF(Product, ''), 'No_Product') AS Product,
ISNULL(NULLIF(Type, ''), 'No_Type') AS Type,

Open in new window

In the case, when it's only empty string, then the CASE expression should perform slightly better than two function calls.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elwayisgod

ASKER

Uh oh... You're a SQL guy too? LOL

Bill Prew

Sam,

It's my prior life as a DBA showing through... 😉

»bp

elwayisgod

ASKER

Thanks all, I didn't realize it could be part of the SELECT like that. Pretty cool. Thanks for all the help!!

awking00

A good place to use Coalesce -
SELECT Currency,
Year_of_Extract AS Years,
Customer,
Entity,
Coalesce(Product,'No_Product') AS Product,
Scenario,
Coalesce(Type,'No_Type') AS Type
VIEW1 AS 'View',
Month_of_Extract AS Period,
Account,
Value as Data
FROM . . .
WHERE . . .

Bill Prew

COALESCE is a great thought, but the author mentioned that the columns that are "empty" don't seem to actually have NULL in them, but rather and empty string (''). Which I would agree, seems a little odd, so it might be worth a double check to verify that.

Sam, you can look it up (I know you work in Oracle, but this question tags SQL Server), but the basic idea of COALESCE is it takes a list of parms and returns the first one in that list that is not NULL valued.

SQL Server COALESCE() Function

»bp

elwayisgod

ASKER

Ok. So in SSMS the Coalesce did not change the empty/null fields to desired value. Thus not sure it will work in this case. I'm not sure why NULL doesn't work.....

Bill Prew

It's just a function of how the data was populated into those columns. Some applications place NULL into "empty" fields, others place an empty string for string fields. I'd say NULL is maybe more common, but the other does exist in the wild...

»bp

awking00

I know it's mixing functions but coalesce could be used in conjunction with nullif to convert the empty string to null.