Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Apr 19, 2002

Select Case Example

By DatabaseJournal.com Staff



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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM