Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

SQL Server (2008): CASE Expression

sql server case statement conditional

  • Please log in to reply
4 replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 19 May 2013 - 11:48 AM

How To Use
There are two ways to use CASE. The first, the simple one, inspects only for equality with a single "variable". While the other one inspects arbitrary conditions (as long as they are proper and valid, of course). This one usually referred as Searched CASE.


Simple CASE Expression
The syntax of this Simple CASE Expression is:



CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

The input_expression was the "variable" I mentioned previously.

For example, let's say that we have an EmailAddresses table like this:
 



-------------------------------------
Address             RefCount
=====================================
john@myemail.com       2
jane@janemail.com      1
robert@robertmail.com  0

 
Column RefCount explains how many member use the email address. In this example, we want to map the RefCount into simpler message, i.e one of these: "Not used" (for those with RefCount equals 0), "In use" (for those with RefCount of 1), and "Multi use" (for those with RefCount greater than 1).

To accomplish this, we use CASE expression in SELECT statement like shown below.



SELECT
  Address
  , [Status]=CASE RefCount
               WHEN 0 THEN 'Not used'
               WHEN 1 THEN 'In use'
               ELSE 'Multi use'
             END
FROM
  EmailAddresses

When used agains EmailAddresses table with the above data the above SELECT will return this result set.



-------------------------------------
Address                 Status
=====================================
john@myemail.com        Multi use
jane@janemail.com       In use
robert@robertmail.com   Not used

Note that we resort to use ELSE to map RefCount greater than 1 to 'Multi use' since simple CASE only able to check for equality, not other condition. So if our EmailAddresses contains RefCount with negative value (like the following sample data), our previous SELECT will return incorrect information.
 



-------------------------------------
Address                 RefCount
=====================================
john@myemail.com        2
jane@janemail.com       1
robert@robertmail.com   0
genie@doe.com           -2

And the previous simple CASE will return the following incorrect information.
 



-------------------------------------
Address                Status
=====================================
john@myemail.com       Multi use
jane@janemail.com      In use
robert@robertmail.com  Not used
genie@doe.com          Multi use

This is why we should use searched CASE for this kind of situation.


Searched CASE Expression
The syntax would be:



CASE
    WHEN Boolean_expression THEN result_expression [ ...n ]
    [ ELSE else_result_expression ]
END

With searched CASE we can virtually examine anything against any condition. The database engine will inspect each WHEN expression in the order they were given until found one which expression returned true. If there were no WHEN expression returning true, the database engine will either use the value returned by the ELSE expression or simply spits out NULL when there is no ELSE expression defined.

For example, for the previous problem (the one when table EmailAddresses contains -2 in RefCount) we ca perfectly solve it using the following SELECT armed with searched CASE.
 



SELECT
  Address
  , [Status]=CASE
               WHEN RefCount=0 THEN 'Not used'
               WHEN RefCount=1 THEN 'In use'
               WHEN RefCount>1 THEN 'Multi use'
               ELSE 'Invalid'
             END
FROM
  dbo.EmailAddresses

That SELECT will return:



-------------------------------------
Address                Status
=====================================
john@myemail.com       Multi use
jane@janemail.com      In use
robert@robertmail.com  Not used
genie@doe.com          Invalid

See? Problem solved.

The following SELECT illustrates how each WHEN expression does not need to be related with each other.
 



SELECT
  Address
  , [Status]=CASE
               WHEN LEFT(Address, 2)='jo' THEN 'Used by JO'
               WHEN RefCount=0 THEN 'Not used'
               WHEN RefCount=1 THEN 'In use'
               WHEN RefCount>1 THEN 'Multi use'
               ELSE 'Invalid'
             END
FROM
  dbo.EmailAddresses;

With the same data like before, that SELECT returns:



-------------------------------------
Address                 Status
=====================================
john@myemail.com        Used by JO
jane@janemail.com       In use
robert@robertmail.com   Not use
Genie@doe.com           Invalid

Edited by Luthfi, 20 May 2013 - 07:46 PM.

  • 1

#2 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 20 May 2013 - 05:30 AM

As a habit, I usually put the field name on the end statement

 

SELECT
Address
,CASE 

    WHEN RefCount=0 THEN 'Not used'

    WHEN RefCount=1 THEN 'In use'

    WHEN RefCount>1 THEN 'Multi use'
    ELSE 'Invalid'
  END 'Status'
FROM
dbo
.EmailAddresses


Edited by lespauled, 20 May 2013 - 05:31 AM.

  • 1
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#3 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 20 May 2013 - 07:21 PM

As a habit, I usually put the field name on the end statement

 

SELECT
Address
,CASE 

    WHEN RefCount=0 THEN 'Not used'

    WHEN RefCount=1 THEN 'In use'

    WHEN RefCount>1 THEN 'Multi use'
    ELSE 'Invalid'
  END 'Status'
FROM
dbo
.EmailAddresses

 

Yeah, that's closer to standard SQL. Initially I used to do that. But later I found that putting the field name before the CASE (or other operation) make the query easier to read. The indentations in that tutorial were deleted by the rich-text editor upon posting, let me see if I can get away with good indentation this time. I usually format my select query like this.

SELECT
  Field1
  , Field2
  , Field3 = CASE
               WHEN Condition1 THEN Result1
               WHEN Condition2 THEN Result2
               ELSE Result3
             END
  , Field4
  , Field5 = (SELECT ...)
  , Field6 = FieldX + FieldY / FieldZ
  , Field7
FROM
  Table1
  ...

  • 0

#4 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 21 May 2013 - 06:19 AM

I got excited for a minute after reading your case syntax.  I wondered if you could use that field in the where clause.

 

like:

 

 

DECLARE @testval int
 
SELECT @testval = 1
 
SELECT status = 
CASE
WHEN @testval = 0 
THEN 'zero'
WHEN @testval = 1
THEN 'one'
ELSE
'not found'
END 
WHERE status is not null
 
Unfortunately, it didn't work any different from the ansi syntax.  Bummer.  Would have been cool.

 

Oh well, back to the drawing board.  :(


  • 0
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#5 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 22 May 2013 - 06:28 AM

Lol. Poor fellow. However I don't see the point in doing that, though. You can just use the CASE condition(s) in your WHERE clause. For your example, you could rewrite that like this.

DECLARE @testval int;
SET @testval=1;
SELECT 
	[Status] = 
		CASE
			WHEN @testval = 0 THEN 'zero'
			WHEN @testval = 1 THEN 'one'
			ELSE
				'not found'
		END
WHERE @testval is not null;

And if you change the value of @testval into NULL you will correctly get empty result set.


  • 0





Also tagged with one or more of these keywords: sql server, case statement, conditional