Conditional Processing with CASE
In this lesson of the SQL tutorial, you will learn...
- To use the CASE function to display different values depending on the values of a column or columns.
Using CASE
CASE functions contain one or more WHEN clauses as shown below.
Syntax
--OPTION 1 SELECT CASE column WHEN VALUE THEN RETURN_VALUE WHEN VALUE THEN RETURN_VALUE WHEN VALUE THEN RETURN_VALUE WHEN VALUE THEN RETURN_VALUE ELSE RETURN_VALUE END AS ColumnName FROM table --OPTION 2 SELECT CASE WHEN EXPRESSION THEN RETURN_VALUE WHEN EXPRESSION THEN RETURN_VALUE WHEN EXPRESSION THEN RETURN_VALUE WHEN EXPRESSION THEN RETURN_VALUE ELSE RETURN_VALUE END AS ColumnName FROM table
Code Sample: Case/Demos/Case.sql
/* Create a report showing the customer ID and company name, employee id, firstname and lastname, and the order id and a conditional column called "Shipped" that displays "On Time" if the order was shipped on time and "Late" if the order was shipped late. */ SELECT c.CustomerID, c.CompanyName, e.EmployeeID, e.FirstName, e.LastName, OrderID, (CASE WHEN ShippedDate < RequiredDate THEN 'On Time' ELSE 'Late' END) AS Shipped FROM Orders o JOIN Employees e ON (e.EmployeeID = o.EmployeeID) JOIN Customers c ON (c.CustomerID = o.CustomerID) ORDER BY Shipped;
Code Explanation
The above SELECT statement will return the following results (not all rows shown).
Code Sample: Case/Demos/Case-GroupBy.sql
/* Create a report showing the customer ID and company name, employee id, firstname and lastname, and the order id and a conditional column called "Shipped" that displays "On Time" if the order was shipped on time and "Late" if the order was shipped late. */ SELECT e.FirstName, e.LastName, COUNT(o.OrderID) As NumOrders, (CASE WHEN o.ShippedDate < o.RequiredDate THEN 'On Time' ELSE 'Late' END) AS Shipped FROM Orders o JOIN Employees e ON (e.EmployeeID = o.EmployeeID) GROUP BY e.FirstName, e.LastName, (CASE WHEN o.ShippedDate < o.RequiredDate THEN 'On Time' ELSE 'Late' END) ORDER BY e.LastName, e.FirstName, NumOrders DESC;
Code Explanation
The above SELECT statement will return the following results.
Notice how the GROUP BY clause contains the same CASE statement that is in the SELECT clause. This is required because all non-aggregate columns in the SELECT clause must also be in the GROUP BY clause and the GROUP BY clause cannot contain aliases defined in the SELECT clause.
Exercise: Working with CASE
Duration: 10 to 15 minutes.
In this exercise you will practice using CASE.
- Create a report that shows the company names and faxes for all customers. If the customer doesn't have a fax, the report should show "No Fax" in that field as shown below.
Conditional Processing with CASE Conclusion
In this lesson of the SQL tutorial, you learned about using CASE to output different values in reports based on data contained in table fields.
To continue to learn SQL go to the top of this page and click on the next lesson in this SQL Tutorial's Table of Contents.
Last updated on 2009-05-11