The fundamental makeup of structured query language (SQL) is to "select" the data you're looking for, "from" the data source you want,"where" specific parameters are met. But the results aren't always easy to understand. When writing your select statement, you can creatively customize your report with functions and additional select statements. Let's look at using a CASE function in our SQL select statement.
What is a CASE function?
A CASE function allows you to display specific outputs based on parameters being met, not just the actual data within the parameters. Think of the popular content distribution service If This Then That. The IFTT services uses the CASE function to aggregate content by saying "if" this parameter is met, "then" publish "this" article.
Let's look at a sample table in a database (Table A). We'll call it the "customers" table. It has all the customer information we're looking for, but we want to differentiate the VIP customers from the standard customers.
Table A: Customers
Notice the data structure for this table has the customer status denoted as a digit. In this instance, 1 denotes VIP status and 2 denotes standard status. If you were to send the output of this table to an executive, you'd probably get asked, "What do the ones and twos mean?" Prevent that question from coming up by using a CASE function in your select. Here's how.
Select cust_id, last_name, first_name, city, CASE IF status = '1' THEN "VIP" ELSE "STANDARD" END
By adding the CASE function to the select statement, you can have the 1s displayed as VIP on the report and the 2s as STANDARD. When using CASE, you have to have the "if" clause mentioned, followed by a "then" clause. In short, you're typing "if this parameter is met, then output what I want to the report." I'll show it here with notation to further explain:
CASE /* starting the CASE function */
IF /* put your "if" here */
Status = '1' /* specify which field in the database table and the parameter */
THEN 'VIP' /* specify what to display when your "if" condition is met */
ELSE 'STANDARD' /* specify what to display if your condition isn't met */
END /* this terminates the CASE function */
The beauty of the CASE function is you can have your code roll with more than one IF/THEN statement. If my sample table had more than two values for the customer status, I could have easily specified what to display by continuing with consecutive IF/THEN statements until all conditions were met. As usual, close out the function with the END statement.
CASE IF status = '1' THEN 'VIP'
ELSE IF status = '2' THEN 'STANDARD'
ELSE IF status = '3' THEN 'FAMILY'
One final note. If you run this SQL with the CASE function, the report you generate will list each column you mentioned in your select statement, but the column designated for the CASE function will denote as "expression". To remedy this, just add a label after you terminate the CASE function.
SELECT last_name, state_code, CASE IF status = '1' THEN 'VIP' ELSE 'STANDARD' END as status
The CASE function offers an easy way to present data found in a database table in a more user-friendly format. Let's be honest: Today's enterprise data models have a gazillion tables with several dependencies and keys that act as headers to more detailed information. The individuals requesting the report will not want to review a report that's full of "tech talk." They'll want data that's valuable to them and easy to understand. Allow CASE to do this for you.
- NoSQL keeps rising, but relational databases still dominate big data
- Microsoft Power BI: The smart person's guide
- How to use blockchain to build a database solution
- Cloud database growth may be slowing as lock-in fears loom
What SQL tricks do you use to make your reports easier to understand? Share your thoughts and advice with fellow TechRepublic members.
Ant Pruitt is an IT Support Professional with a passion for showing the non-geek how great technology can be. He writes for a variety of tech publications and hosts his own podcast. Ant is also an avid photographer and weight lifter.