How to Use Conditional Processing
A common challenge in writing an SQL select statement is checking a condition and then producing an appropriate result. For example, with a rollup operation on a
group by we usually need to determine if the report is displaying a group total or a grand total. The
decode function allows us to accomplish this
decode function gives the SQL developer an important tool for implementing conditional processing.
To learn how to use the conditional processing, 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.
- Our requirement is to display the count of employees in each department according to the
Employeestable and to include a grand total by using the
rollupfunction. We also need to clearly identify department totals from the grand total. Type in the following query and then hit Enter:
Note that I have used the SQL*Plus
colstatement to format the output for improved readability. The
decodefunction will check the value returned by the
groupingfunction. If the value is 0 then the report line represents a department total and therefore we will print the department ID. If the value returned by the
groupingfunction is 1, then the report line represents a grand total and therefore we will print the word "All".