How to Use the Case Expression

The case expression gives us the ability to convert column data referenced on a SQL select statement to more meaningful values. For example, we might wish to convert location IDs in the Departments table to actual city names. Of course, we could join the Departments table to the Locations table but why incur the overhead of a join if we can simply provide the city name on the original select? If the mapping of location ID to city name changed quite a bit then a join would be indicated. Otherwise, we should check out the case expression.

To learn how to use the case expression, follow these steps:

  1. You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 before proceeding to the next step.
  2. We presuppose that we are constantly displaying department data from departments located in Seattle, Toronto and London. Furthermore, let's imagine that the end users who view the report are not familiar with location IDs. Therefore we wish to translate the location ID to a meaningful city name (e.g., translate location ID 1800 to Toronto). Type in the following query and then hit Enter:
    Department city name using case expression
    The case expression checks the value of the location_id and translates it to the appropriate city name. I use an alias (City) to give the column a meaningful heading on the report.
Author: Stephen Withrow

Stephen has over 30 years of experience in training, development, and consulting in a variety of technology areas including Python, Java, C, C++, XML, JavaScript, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen has a degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.