Login or Sign Up to become a member!

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Case Without Else

From Wiki

Jump to: navigation, search

i you have a case statement like this

tsqlLine number Off | Hide | Select all

SELECT
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS

This still returned NULLS. Can you spot the flaw? There is no else statement, if there is a value which is not sugar, salt or pepper then a NULL will be returned. let's take a look.

Create this table

tsqlLine number Off | Hide | Select all

CREATE TABLE #INV_ITEMS (COL1 VARCHAR(23))
INSERT #INV_ITEMS VALUES('SUGAR')
INSERT #INV_ITEMS VALUES('SALT')
INSERT #INV_ITEMS VALUES('PEPPER')
INSERT #INV_ITEMS VALUES('WASABI')

Now run this

tsqlLine number Off | Hide | Select all

SELECT
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS

Output

SGR

SAL

PEP

NULL

So we get a NULL, but which row is that? We can just add the column to see the original value

tsqlLine number Off | Hide | Select all

SELECT
COL1,CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS

Output

SUGAR	SGR
SALT	SAL
PEPPER	PEP
WASABI	NULL

Aha, it is the wasabi.

Let's try again by including an ELSE

tsqlLine number Off | Hide | Select all

SELECT
COL1,CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
ELSE 'UNK'
END AS items
FROM #INV_ITEMS

Output

SUGAR	SGR
SALT	SAL
PEPPER	PEP
WASABI	UNK

There we go, it is correct now.

Now let's make it more interesting by inserting a blank, a space and a NULL

tsqlLine number Off | Hide | Select all

INSERT #INV_ITEMS VALUES('')
INSERT #INV_ITEMS VALUES(' ')
INSERT #INV_ITEMS VALUES(NULL)

We can use a combination of NULLIF and RTRIM to filter out blanks, spaces and NULLs

tsqlLine number Off | Hide | Select all

SELECT
COL1,CASE
WHEN NULLIF(RTRIM(COL1),'') IS NULL THEN 'NullOrBlank'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
ELSE 'UNK'
END AS items
FROM #INV_ITEMS

Output

SUGAR	SGR
SALT	SAL
PEPPER	PEP
WASABI	UNK
	NullOrBlank
	NullOrBlank
NULL	NullOrBlank

And that is all

tsqlLine number Off | Hide | Select all

DROP TABLE #INV_ITEMS

Contributed by: --SQLDenis 03:21, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Pitfalls

Currently 1.89/5
1
2
3
4
5

193 Rating: 1.9/5 (9 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Case_Without_Else"