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 task. The decode function gives the SQL developer an important tool for implementing conditional processing.

To learn how to use the conditional processing, 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. Our requirement is to display the count of employees in each department according to the Employees table and to include a grand total by using the rollup function. We also need to clearly identify department totals from the grand total. Type in the following query and then hit Enter:
    Employee count by department with decode function
    Note that I have used the SQL*Plus col statement to format the output for improved readability. The decode function will check the value returned by the grouping function. 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 grouping function is 1, then the report line represents a grand total and therefore we will print the word "All".
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.