Login or Sign Up to become a member!
LessThanDot Site Logo

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Case Without Else

From Wiki

Jump to: navigation, search

i you have a case statement like this

  1. SELECT
  2.     CASE
  3.     WHEN COL1 IS NULL THEN 'UNK'
  4.     WHEN COL1 = 'SUGAR' THEN 'SGR'
  5.     WHEN COL1 = 'SALT' THEN 'SAL'
  6.     WHEN COL1 = 'PEPPER' THEN 'PEP'
  7.     END AS items
  8.     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

  1. CREATE TABLE #INV_ITEMS (COL1 VARCHAR(23))
  2.     INSERT #INV_ITEMS VALUES('SUGAR')
  3.     INSERT #INV_ITEMS VALUES('SALT')
  4.     INSERT #INV_ITEMS VALUES('PEPPER')
  5.     INSERT #INV_ITEMS VALUES('WASABI')



Now run this

  1. SELECT
  2.     CASE
  3.     WHEN COL1 IS NULL THEN 'UNK'
  4.     WHEN COL1 = 'SUGAR' THEN 'SGR'
  5.     WHEN COL1 = 'SALT' THEN 'SAL'
  6.     WHEN COL1 = 'PEPPER' THEN 'PEP'
  7.     END AS items
  8.     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


  1. SELECT
  2.     COL1,CASE
  3.     WHEN COL1 IS NULL THEN 'UNK'
  4.     WHEN COL1 = 'SUGAR' THEN 'SGR'
  5.     WHEN COL1 = 'SALT' THEN 'SAL'
  6.     WHEN COL1 = 'PEPPER' THEN 'PEP'
  7.     END AS items
  8.     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

  1. SELECT
  2.     COL1,CASE
  3.     WHEN COL1 IS NULL THEN 'UNK'
  4.     WHEN COL1 = 'SUGAR' THEN 'SGR'
  5.     WHEN COL1 = 'SALT' THEN 'SAL'
  6.     WHEN COL1 = 'PEPPER' THEN 'PEP'
  7.     ELSE 'UNK'
  8.     END AS items
  9.     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



  1. INSERT #INV_ITEMS VALUES('')
  2.     INSERT #INV_ITEMS VALUES(' ')
  3.     INSERT #INV_ITEMS VALUES(NULL)



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

  1. SELECT
  2.     COL1,CASE
  3.     WHEN NULLIF(RTRIM(COL1),'') IS NULL THEN 'NullOrBlank'
  4.     WHEN COL1 = 'SUGAR' THEN 'SGR'
  5.     WHEN COL1 = 'SALT' THEN 'SAL'
  6.     WHEN COL1 = 'PEPPER' THEN 'PEP'
  7.     ELSE 'UNK'
  8.     END AS items
  9.     FROM #INV_ITEMS


Output

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



And that is all

  1. DROP TABLE #INV_ITEMS


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

Part of SQL Server Programming Hacks

Section Pitfalls

193 Rating: 2.3/5 (98 votes cast)