facebook google plus twitter
Webucator's Free Advanced Oracle SQL Queries Tutorial

Lesson: Conditional Processing

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based on Webucator's Advanced Oracle SQL Queries course.

Many programming languages have the concept of conditional processing. Among the most common construct is an if/else structure. Oracle SQL does allow for conditional processing, but does not use if/else statements to accomplish it. Instead, the DECODE() function and a CASE statement are provided.

Lesson Goals

  • Learn about the DECODE() function.
  • Learn about CASE statement.

The DECODE() Function

A familiar conditional construct is the IF/THEN ELSE construct is shown below in pseudocode:

IF <some boolean condition> THEN
	<do something>
ELSE IF <some other boolean condition>
	<do something else>
ELSE
	<do default action>

This type of functionality is not available in Oracle SQL. However, the DECODE() function can be used for the same purpose.

The DECODE() function takes as its first argument a base expression. That argument is followed by any number of argument pairs. Think of each pair as an if-then statement: if the first argument in the pair is equal to the base, then return the second argument in the pair.

For example, the following statement would return 'YES':

DECODE('foo', 'foo', 'YES')

And the following statement would return 'NO':

DECODE('foo', 'bar', 'YES', 'foo', 'NO')

DECODE() can have an optional final argument that is the value that will be returned if no matches for the base value are found. The following statement would return 'Golly!', because neither 'bar' nor 'oof' is equal to 'foo':

DECODE('foo', 'bar', 'YES', 'oof', 'NO', 'Golly!')

In the demonstration below, the relevant arguments listed are as follows:

  • Argument 1: 1 as the base expression
  • Argument 2: 1 as the search expression for the first pair
  • Argument 3: 'option 1' as the result expression for the first pair
  • Argument 4: 2 as the search expression for the second pair
  • Argument 5: 'option 2' as the result expression for the second pair
  • Argument 6 (if included): the default value

The DECODE() function can be better understood by doing a few tests with hard-coded values against the dual table:

Code Sample:

Conditional-Processing/Demos/decode_with_dual.sql
-- The example below returns 'option 1' (argument 3)
		-- because the base expression of 1 (argument 1) matches the
		-- search expression (1) in the first pair (which consists
		-- of arguments 2 and 3).
		SELECT DECODE(1,1,'option 1',2,'option 2','anything else') AS result 
		FROM dual;
		
		-- The example below uses 2 as the base expression,
		-- and so returns option 2.
		SELECT DECODE(2,1,'option 1',2,'option 2','anything else') AS result 
		FROM dual;
		
		-- The example below uses 99 as the base expression.
		-- Since none of the options match, the default value
		-- ('anything else') is returned.
		SELECT DECODE(99,1,'option 1',2,'option 2','anything else') AS result 
		FROM dual;
		
		-- This last example has no default value specified.
		-- No match is made, and NULL is returned.
		SELECT DECODE(99,1,'option 1',2,'option 2') AS result 
		FROM dual;

In some cases, indentation can clarify the logic involved. This is clearly seen when multiple functions are nested together or a large numbers of arguments are required.

Having seen the use of decode with literals in the previous example, it is easier to understand the use of the function with fields in tables.

In the following example, we compare an unformatted (without indenting) query with a formatted query. The query returns 'THE EXECUTIVES!' if the department name is 'Executive'; otherwise, it returns the actual name of the department.

Code Sample:

Conditional-Processing/Demos/decode_dept_name.sql
-- Unformatted...
	SELECT DECODE(department_name, 'Executive', 'THE EXECUTIVES!', department_name) AS dept 
	FROM departments;
	
	-- Formatted...
	SELECT 
		DECODE(
			department_name, 
			'Executive', 
			'THE EXECUTIVES!', 
			department_name
		) AS dept
	FROM departments;

DECODE() not only can evaluate literals and simple field values; it also can deal with expressions. In the following example, the default value returns a calculated value representing the percentage commission for each employee:

Code Sample:

Conditional-Processing/Demos/decode_commission.sql
SELECT 
			first_name, 
			last_name, 
			DECODE	(	commission_pct, 
						NULL, 'None', 
						commission_pct * 100  ||'%' 
					) AS commission_info 
		FROM employees
		ORDER BY first_name, last_name;

The Case Statement

The CASE statement can function in exactly the same way as the DECODE() function. The syntax is as follows:

CASE expression
    WHEN match_test2 THEN result1
    WHEN match_test2 THEN result2
    WHEN match_test2 THEN result3
    ELSE default_result
END

Looking at the DECODE() example we saw earlier:

DECODE('foo', 'bar', 'YES', 'oof', 'NO', 'Golly!')

This could be written as a CASE statement like this:

CASE 'foo'
    WHEN 'bar' THEN 'YES'
    WHEN 'oof' THEN 'NO'
    ELSE 'Golly!'
END

Here is an example using the employees table:

Code Sample:

Conditional-Processing/Demos/case_expression.sql
SELECT first_name, last_name, 
	CASE first_name 
		WHEN 'Adam' THEN 'is first'
		WHEN 'Alana' THEN 'is next'
		ELSE 'follows' 
	END AS statement
FROM employees
ORDER BY first_name, last_name;

In addition to checking for exact matches, a CASE statement can be used to evaluate ranges of data:

Code Sample:

Conditional-Processing/Demos/case_expression_range.sql
SELECT 
	first_name, 
	last_name, 
	salary, 
	CASE  
  		WHEN salary > 20000 THEN 'very high'
  		WHEN salary > 10000 THEN 'high'
  		WHEN salary < 2600 THEN 'low'
  		ELSE  'average'
  	END AS category
FROM employees
ORDER BY first_name, last_name;

Conditional Processing

Duration: 20 to 30 minutes.
  1. Start with a simple query that retrieves the street_address, postal_code, city, state_province and country_id from the locations table.
  2. Add a DECODE() function call that checks the country_id. If the value of country id is 'US' then display the word 'Domestic' otherwise display the word 'Foreign'.
  3. Create a query with an inner join on the employees and jobs tables.
  4. In the query's SELECT statement, choose the first_name, last_name, job_title, salary increased by 10%, the min_salary and the max_salary.
  5. Add a CASE statement (aliased as new_job_title) that appends the word 'Senior' to the title for any employee who's salary when adjusted by 10% exceeds the max_amount permitted for the job title.

Solution:

Conditional-Processing/Solutions/conditional_processing_solutions.sql
--Simple statement
SELECT 
	street_address, 
	postal_code, 
	city, 
	state_province, 
	country_id
FROM locations;

--Adding the DECODE statement
SELECT 
	street_address, 
	postal_code, 
	city, 
	state_province, 
	country_id, 
	DECODE(country_id,'US', 'Domestic','Foreign') location_area
FROM locations;

SELECT 
	first_name, 
	last_name, 
	j.job_title, 
	salary * 1.10 AS amount, 
	min_salary, 
	max_salary 
FROM employees e 
INNER JOIN jobs j ON e.job_id = j.job_id;

SELECT 
	first_name, 
	last_name, 
	j.job_title AS old_title,
	CASE 
		WHEN salary * 1.10 > max_salary THEN 'Senior '||job_title
		ELSE job_title
	END AS new_job_title
FROM employees e 
INNER JOIN jobs j ON e.job_id = j.job_id
-- If you want to only see records that changed, uncomment
-- the following line
--WHERE salary * 1.10 > max_salary
;