The CASE function allows you to evaluate a column value on a row against multiple criteria, where each criterion might return a different value. The first criterion that evaluates to true will be the value returned by the CASE function. Microsoft SQL Server Books Online documents two different formats for the CASE function. The "Simple Format" looks like this:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
And the "Searched Format"
looks like this: CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Where the "input_expression" is any valid Microsoft SQL Server expression, the "when_expression" is the value in which the input_expression is compared, the "result_expression" is the value that will be return for the CASE statement if the "when_expression" evaluates to true, "[...n]" represents that multiple WHEN conditions can exist, the "else_result_expression" is the value that will be returned if no "when_expression" evaluates to true and in the "Searched Format" the "Boolean_expression" is any Boolean express that when it evaluates to true will return the "result_expression". Let me go through a couple of examples of each format to help you better understand how to use the CASE function in a SELECT statement.
For the first example let me show you how you would use the CASE function to display a description, instead of a column value that contains a code. I am going to use my earlier example that I described at the top of this article where I discussed displaying "female" or "male" instead of the SexCode. Here is my example T-SQL Code:
create table patients (PatientID int identity,
PatientName varchar(50),
PatientSexCode int)
insert into patients(PatientName, PatientSexCode)
values ('Doe, Jane', 0)
insert into patients(PatientName, PatientSexCode)
values ('Doe, Dick', 1)
insert into patients(PatientName, PatientSexCode)
values ('Doe, Spot', 2)
insert into patients(PatientName, PatientSexCode)
values ('xxxxxxxx', 9)
select PatientName,
case PatientSexCode
when 0 then 'female'
when 1 then 'male'
when 2 then 'unknown'
else 'Invalid PatientSexCode' end as "Patient Sex"
from patients
Here is the output from this T-SQL code:
PatientName Patient Sex --------------------------- ----------------------- Doe, Jane female Doe, Dick male Doe, Spot unknown xxxxxxxx Invalid PatientSexCode
This example shows the syntax in action for a CASE function using the "Simple Format". As you can see the CASE function evaluates the PatientSexCode to determine if it is a 0, 1, or 2. If it is a 0, then "female" is displayed in the output for the "Patient Sex" column. If the PatientSexCode is 1, then "male" is display, or if PatientSexCode is 2 then "unknown" is displayed. Now if the PatientSexCode is anything other than a 0, 1 or 2 then the "ELSE" condition of the CASE function will be used and "Invalid PatientSexCode" will be displayed for the "Patient Sex" column.
Now the same logic could be written using a "Searched Format" for the CASE function. Here is what the SELECT statement would look like for the "Searched Format":
select PatientName,
case
when PatientSexCode = 0 then 'female'
when PatientSexCode = 1 then 'male'
when PatientSexCode = 2 then 'unknown'
else 'Invalid PatientSexCode' end as "Patient Sex"
from patients
Note the slight differences between the "Simple" and "Searched" formats. In the "Simple" format I specified the column name for which row values will be compared against the "when_expressions" ,where as in the "Searched" format each WHEN condition contains a Boolean expression that compares the PatientSexCode column against a code value.
Now the CASE function can be considerably more complex than the basic examples I have shown. Suppose you want to display a value that is based on two different columns values in a row. Here is an example that determines if a Product in the Northwind database is of type Tins or Bottles, and is not a discontinued item.
Select top 8 ProductName,
case
when QuantityPerUnit like '%Tins%' and
Discontinued = 0
then 'Tins'
when QuantityPerUnit like '%bottles%' and
Discontinued = 0
then 'Bottles'
else 'Not Tins, Not Bottles, or is Discontinued'
end 'Type or Availability'
from Northwind.dbo.Products
order by ProductName
The output for the above command on my server displays the following:
ProductName Type or Availability ----------------------- ----------------------------------------- Alice Mutton Not Tins, Not Bottles, or is Discontinued Aniseed Syrup Bottles Boston Crab Meat Tins Camembert Pierrot Not Tins, Not Bottles, or is Discontinued Carnarvon Tigers Not Tins, Not Bottles, or is Discontinued Chai Not Tins, Not Bottles, or is Discontinued Chang Bottles Chartreuse verte Not Tins, Not Bottles, or is Discontinued
As you can see I'm using a "Searched Format" for this CASE function call. Also, each WHEN clause contains two different conditions. One condition to determine the type (tins, or bottles) and another condition to determine if the product has been discontinued. If the QuantityPerUnit contains the string "Tins" and the Discontinue column value is 0 then the "Type of Availability" is set to "Tins". If the QuantityPerUnit contains the string "Bottles" and the Discontinue column value is 0 then the "Type of Availability" is set to "Bottles". For all other conditions, the "Type or Availability" is set to "Not Tins", "Not Bottles", or is "Discontinued."
The WHEN clauses in the CASE function are evaluated in order. The first WHEN clause that evaluates to "True" determines the value that is returned from the CASE function. Basically, multiple WHEN clauses evaluate to "True", only the THEN value for the first WHEN clause that evaluates to "True" is used as the return value for the CASE function. Here is an example where multiple WHEN clauses are "True."
select top 5 title,
case
when price < 12.00 then 'Cheap'
when price < 3.00 then 'Really Cheap'
when price > 12.00 and price < 20.00 then 'Average'
else 'Expensive' end 'Price Category'
from pubs.dbo.titles
The output on my machine for this query looks like this:
title Price Category -------------------------------------------------------- ------------ The Busy Executive's Database Guide Average Cooking with Computers: Surreptitious Balance Sheets Cheap You Can Combat Computer Stress! Cheap Straight Talk About Computers Average Silicon Valley Gastronomic Treats Average
If you look at the raw titles table data in the pubs database for the title "You Can Combat Computer Stress!" you will note that the price for this book is $2.99. This price makes both the "price < 12.00" and "price < 3.00" conditions "True". Since the conditions are evaluated one at a time, and the "price < 12.00" is evaluated prior to the "price < 3.00," the "Price Category" for the title "You Can Combat Computer Stress!" is set to "Cheap".
The CASE function can appear in different places within the SELECT statement, it does not have to only be in the selection list within the SELECT statement. Here is an example where the CASE function is used in the WHERE clause.
select top 5 title, price
from pubs.dbo.titles
where
case
when price < 12.00 then 'Cheap'
when price < 3.00 then 'Really Cheap'
when price > 12.00 and price < 20.00 then 'Average'
else 'Expensive' end = 'Average'
The output for this query looks like this:
title price -------------------------------------------------------- -------------- The Busy Executive's Database Guide 19.9900 Straight Talk About Computers 19.9900 Silicon Valley Gastronomic Treats 19.9900 Prolonged Data Deprivation: Four Case Studies 19.9900 Sushi, Anyone? 14.9900
Here I only wanted to display books from the titles table in pubs database if the price category is 'Average'. By placing my CASE function in the WHERE clause I was able to accomplish this.
Conclusion
As you can see the CASE function is an extremely valuable function. It allows you to take a data column and represent it differently depending on one or more conditions identified in the CASE function call. I hope that the next time you need to display or use different values for specific column data you will review the CASE function to see if it might meet your needs.source : http://www.databasejournal.com/features/mssql/article.php/3288921/T-SQL-Programming-Part-5---Using-the-CASE-Function.htm
Tidak ada komentar:
Posting Komentar