How do I exclude null when I returning specific rows based on conditions-CodePudding

I was have Channel column with saving, checking and retirement rows. I wanted return rows with only saving and checking. exclude retirement rows

CodePudding user response：

Add a where clause with WHERE channel IS NOT NULL.

As long as you don't change the values for your result set, you can remove the CASE WHEN. Just write

SELECT acctno, channel FROM accounts_profile 
WHERE channel IS NOT NULL

A CASE WHEN construct is required only when you want to change the original values and show other values based on conditions, as example something like:

SELECT acctno, 
CASE WHEN channel = 'Saving' THEN 's'
ELSE 'c' END AS channel 
FROM accounts_profile 
WHERE channel IS NOT NULL

If you want to replace NULL values by another value, use COALESCE:

SELECT acctno, COALESCE(channel,'NotFound') FROM accounts_profile

If you want to get channels with 'Saving' or 'Checking' only, write a simple WHERE clause:

SELECT acctno, channel FROM accounts_profile
WHERE channel IN ('Saving','Checking');

CodePudding user response：

If you want to exclude null in where clause

SELECT AcctNo,
CASE
WHEN Channel = 'Saving' THEN Channel
WHEN Channel = 'Checking' THEN Channel
END AS Channel
FROM Account_Profile
WHERE Channel IS NOT NULL

If you want blank if channel is null in result

SELECT AcctNo,
CASE
WHEN Channel = 'Saving' THEN Channel
WHEN Channel = 'Checking' THEN Channel
ELSE '' END AS Channel
FROM Account_Profile

But, I would select like

SELECT acctno, channel FROM Account_Profile WHERE Channel IS NOT NULL