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:
- 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.
- 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:
The case expression checks the value of the
location_idand translates it to the appropriate city name. I use an alias (
City) to give the column a meaningful heading on the report.