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.
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.
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:
DECODE() function can be better understood by doing a few
tests with hard-coded values against the
-- 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.
-- 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:
SELECT first_name, last_name, DECODE ( commission_pct, NULL, 'None', commission_pct * 100 ||'%' ) AS commission_info FROM employees ORDER BY first_name, last_name;
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
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:
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;
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'.
SELECTstatement, choose the
salaryincreased by 10%, the
CASEstatement (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_amountpermitted for the job title.
--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 ;