Select Case Example

Select Case allows you to present data in various ways. You can take numbers and return text values and express certian conditions based on the value of a certain column.

Another example would be that you have a table “Naughty_Nice” in the Santa Database, with a data column Nice (INT Datatype). There are two distinct values in the column. The Value of 0 equals ‘No’ and the value 1 equals yes. You wanted to speed stuff up and use less space by selecting a number and not use text Naughty/Nice.

Santa needs a list and he complains that 1 and 0 will not make sense to him at 30,000 feet. It’s so hard to communicate to a guy that still thinks wooden toys are a big hit. He’s a bit too hands-on, and lacks the technical skills to really understand that we want to optimize the database.

You want to query the table to find out which child is Naughty and which is Nice, but return a value that ‘Ole Red’ will benefit from. So you quickly point out that a New Yankee Workshop Maraton is on and Norm is demonstrating some $200,000 combination tablesaw-sander with an auto-jig thingy. Santa takes off, giving you time to write the following query.

Select 	c.ChildName,
	g.Gift,
	l.Location,
	case(n.Nice)
	when 1 then ‘Nice’
	Else
	‘Naughty’
	 End
From
Child c, Gift G, Location L, Naughty_Nice n
Where
c.childid=g.childid
and
c.childid=n.childid
Order by
N.Nice N.nice, l.location,g.Gift, c.ChildName

You followed Normalization Rules that would join the tables Child, Gift, Location and Naughty_Nice by Childid. You are Sooooo Good!

Below is an example that is a bit more complex, it takes a peek at the Orders table in the MS SQL Server NorthWind Database. Drop off in June 1998? Hummh? I guess Pubs sold out to a major online bookseller or was pushed out of business by slow sales.

—This example uses the SQL Server Example Database NorthWind
—Purpose: Define by Month and Year Total Orders for Years 1996-1998
—Use Case Function to gather Totals by Year
—Example By Jack Donnell jack@jackdonnell.com 
USE NorthWind
GO
USE NorthWind
GO
SELECT
  MONTH(OrderDate) AS Month,
  SUM(CASE YEAR(OrderDate)
        WHEN 1996 THEN 1
        ELSE 0
      END) AS Orders1996,
  SUM(CASE YEAR(OrderDate)
        WHEN 1997 THEN 1
        ELSE 0
      END) AS Orders1997,
  SUM(CASE YEAR(OrderDate)
        WHEN 1998 THEN 1
        ELSE 0
      END) AS Orders1998
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)
COMPUTE SUM(SUM(CASE YEAR(OrderDate)
        WHEN 1996 THEN 1
        ELSE 0
      END) ),
SUM(SUM(CASE YEAR(OrderDate)
        WHEN 1997 THEN 1
        ELSE 0
      END)),
sum(SUM(CASE YEAR(OrderDate)
        WHEN 1998 THEN 1
        ELSE 0
      END))

The output Would be :

Month       Orders1996  Orders1997  Orders1998
———– ———– ———– ———–
1           0           33          55
2           0           29          54
3           0           30          73
4           0           31          74
5           0           32          14
6           0           30          0
7           22          33          0
8           25          33          0
9           23          37          0
10          26          38          0
11          25          34          0
12          31          48          0
            sum
            ===========
            152
                        sum
                        ===========
                        408
                                    sum
                                    ===========
                                    270
(13 row(s) affected)

Author: Jack C. Donnell

Download Script:
selectcase.txt

Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home