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/then structure. Oracle SQL does allow for conditional processing, but does not use if/then statements to accomplish it. Instead, the DECODE function and a CASE statement are provided.

Lesson Goals

  • Learn about the DECODE function.
  • Learn about case expression.

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

The DECODE Function

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

Code Sample:

Conditional-Processing/Demos/decode_with_dual.sql
SELECT DECODE(1,1,'option 1',2,'option 2','anything else') AS result 
FROM dual;

SELECT 
	DECODE(2,1,'option 1',2,'option 2','anything else') AS result 
FROM dual;

SELECT 
	DECODE(99,1,'option 1',2,'option 2','anything else') AS result 
FROM dual;

SELECT 
	DECODE(99,1,'option 1',2,'option 2') AS result 
FROM dual;

The DECODE statement can be better understood by doing a few tests with hard-coded values against the dual table.

The first example 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).

The next example uses 2 as the base expression, and so returns option 2.

The following example uses 99 as the base expression. Since none of the options match, the default value ('anything else') is returned.

The last example has no default value specified. No match is made, and NULL is returned.

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.

Code Sample:

Conditional-Processing/Demos/decode_dept_name.sql
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;

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.

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;

DECODE can not only evaluate literals or simple field values. It also can deal with expressions. In this example, the default value returns a calculated value representing the percentage commission for each employee.

The Case Expression

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;

The CASE statement can function in exactly the same way as DECODE.

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;

Besides doing exact matches, a CASE can be used to evaluate ranges of data.

Conditional Processing

Duration: 5 to 10 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 by new 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
;