A conditional expression is an advanced form of the value
expression that allows applications to return alternate
information within an expression. See Value_Expressions for more
information on the syntax. The following table describes the
conditional expressions that are supported by Oracle Rdb.
Table 3 Conditional Expressions
Expression
Name Description
ABS ABS returns the absolute value of n.
CASE CASE alters the result of an expression. CASE can also
generate or convert null values.
COALESCE COALESCE returns the first non-NULL value from a series
of value expressions; otherwise, returns NULL.
DECODE Alternate form of the Case expression.
GREATEST GREATEST returns the greatest non-null value.
LEAST LEAST returns the least non-null value.
NULLIF NULLIF substitutes NULL when two value expressions are
equal; otherwise, returns the first value.
NVL NVL returns the first non-NULL value from a series of
value expressions; otherwise, returns NULL. NVL is a
synonym for COALESCE.
NVL2 If the first value expression to NVL2 is not NULL, then
return the second value expression; otherwise return
the third value expression.
SIGN SIGN returns the sign of the value
1 – ABS Function
The ABS function returns NULL if the passed value expression
evaluates to NULL. The datatype of the result is the same as the
passed value expression and supports scaled values of these data
types: TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE
PRECISION, INTERVAL, DECIMAL, NUMERIC and NUMBER.
The absolute value function (ABS) returns NULL if the value
expression evaluates to NULL. If the value expression evaluates
to a value less than zero then that value is negated so that
a positive value is returned. Otherwise the value is returned
unchanged. For instance, ABS (-1) will return the value 1.
ABS (a) is equivalent to the CASE expression
case
when a < 0 then - a
else a
end
Example: Using the ABS function on an INTERVAL result of a date
subtraction.
SQL> select
cont> ABS ((birthday - current_date) year(3))
cont> from employees
cont> order by employee_id
cont> limit to 10 rows;
054
047
047
064
068
062
044
069
050
074
10 rows selected
Example: Using ABS within a statistical function
SQL> -- what is the average time in a job for each employee
SQL> -- exclude anyone on there first job
SQL> select
cont> employee_id,
cont> AVG (ABS (EXTRACT (MONTH FROM (job_start - job_end) month (4))))
cont> as "Average Job" edit using '--,---,--9.99" years"'
cont> from JOB_HISTORY
cont> where employee_id < '00200'
cont> group by employee_id
cont> having COUNT (*) > 1;
EMPLOYEE_ID Average Job
00164 14.00 years
00165 22.67 years
00166 20.00 years
00167 14.50 years
00168 26.33 years
00169 22.67 years
...etc...
00197 26.33 years
00198 37.00 years
00199 35.00 years
30 rows selected
%RDB-I-ELIM_NULL, null value eliminated in set function
2 – COALESCE Expressions
The COALESCE and NVL expressions return the first non-NULL value
from a series of value expressions.
SQL evaluates each value expression in a COALESCE or NVL
expression until it can return a non-NULL value. If all the
columns specified in the COALESCE or NVL expression contain NULL
values, then NULL is returned.
The data type of the resulting expression is a common data type
to which all value expressions in the list can be converted.
For example, COALESCE(SALARY_AMOUNT, ESTIMATED_BONUS, 1.23E+5)
results in a DOUBLE PRECISION result because at least one
argument is a floating point value.
The following example replaces the stored NULL value in the
MIDDLE_INITIAL column of the EMPLOYEES table with a hyphen:
SQL> SELECT FIRST_NAME, LAST_NAME, MIDDLE_INITIAL,
cont> COALESCE(MIDDLE_INITIAL, '-')
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME LIKE 'L%';
FIRST_NAME LAST_NAME MIDDLE_INITIAL
Jo Ann Lapointe C C
Hope Lapointe NULL -
Stan Lasch P P
Norman Lasch NULL -
Peter Lengyel A A
Peter Lonergan V V
6 rows selected
3 – CASE Expressions
There are many situations where you might find it useful to alter
the result of an expression. For example, you might have a table
column called WORK_STATUS containing the data 0, 1, and 2 meaning
Inactive, Full time, and Part time, respectively. The single
character is more efficient to store than the definition of the
character in the database. However, the definition of the single
character is not always intuitive.
There may also be times when you want to generate null
values based on the information derived from the database or,
conversely, convert a null value into a more concrete value like
zero (0). The CASE expressions provide an easy solution to these
problems.
There are two types of CASE expressions:
o Simple-matches two value expressions for equality
o Searched-allows complex predicate, including subqueries
An example of the simple case expression follows:
SQL> SELECT LAST_NAME, FIRST_NAME,
cont> CASE STATUS_CODE
cont> WHEN '1' THEN 'Full time'
cont> WHEN '2' THEN 'Part time'
cont> WHEN NULL THEN 'Unknown'
cont> ELSE 'Inactive'
cont> END
cont> FROM EMPLOYEES;
LAST_NAME FIRST_NAME
Smith Terry Part time
O'Sullivan Rick Full time
.
.
.
Sarkisian Dean Part time
Stornelli James Full time
Hall Lawrence Full time
Mistretta Kathleen Full time
James Eric Inactive
MacDonald Johanna Full time
Dement Alvin Full time
Blount Peter Full time
Herbener James Full time
Ames Louie Full time
100 rows selected
When SQL encounters the first WHEN clause that matches the
primary value expression following the CASE keyword, it evaluates
the THEN clause. If no matching values are found, the ELSE clause
is evaluated. If the ELSE clause is missing, NULL is the returned
value. For example:
SQL> SELECT PRODUCT_NAME,
cont> CASE
cont> WHEN QUANTITY <= 0 THEN 'On back order'
cont> WHEN QUANTITY > 0 THEN
cont> CAST(QUANTITY AS VARCHAR(10)) || ' in stock'
cont> END
cont> FROM INVENTORY;
PRODUCT_NAME
Staples-boxes 20 in stock
Staplers-each 3 in stock
Tape-rolls On back order
Calendars-each 25 in stock
Tape disp.-each On back order
Desk cleaner NULL
6 rows selected
An example of the searched case expression follows:
SQL> SELECT PRODUCT_NAME,
cont> CASE
cont> WHEN QUANTITY <= 0 THEN 'On back order'
cont> WHEN QUANTITY > 0 THEN
cont> CAST(QUANTITY AS VARCHAR(10)) || ' in stock'
cont> ELSE -- must be NULL
cont> 'New Item - awaiting stock'
cont> END
cont> FROM INVENTORY;
PRODUCT_NAME
Staples-boxes 20 in stock
Staplers-each 3 in stock
Tape-rolls On back order
Calendars-each 25 in stock
Tape disp.-each On back order
Desk cleaner New Item - awaiting stock
6 rows selected
The searched case expression allows arbitrary expressions in each
WHEN clause, as shown in the previous example. The simple case
expression is a shorthand method of specifying the searched case
expression.
For the simple and searched case expressions, the data types of
the value expressions of the WHEN clause must be comparable, and
the data types of the value expressions of the THEN clause must
be comparable.
All subqueries in a CASE expression are evaluated. It is the
results of these subqueries that are conditionalized by the CASE
expression and not the actual evaluation.
If any subquery (which must return at most a single row and
column) returns more than one row, the following exception is
generated:
%RDB-E-MULTIPLE_MATCH, record selection criteria should identify only one
record; more than one record found
A workaround is to add one of the following clauses to the
subquery:
o LIMIT TO 1 ROW
This ensures that only one row is returned. For example:
.
.
.
cont> WHEN A IS NOT NULL
cont> THEN (SELECT A FROM T WHERE B = Y
cont> LIMIT TO 1 ROW)
.
.
.
The WHEN condition ignores this row if it is not valid.
o Duplicate the WHEN clause Boolean inside the subquery
predicate
For example:
SQL> --
SQL> -- Change the following syntax from
SQL> --
.
.
.
cont> WHEN A IS NOT NULL
cont> THEN (SELECT A FROM T WHERE B = Y)
.
.
.
SQL> --
SQL> -- to include the Boolean inside the subquery
SQL> --
.
.
.
cont> WHEN A IS NOT NULL
cont> THEN (SELECT A FROM T WHERE B = Y AND A IS NOT NULL)
.
.
.
In this example, when the WHEN clause evaluates as FALSE, so
will the WHERE predicate from the subquery and, therefore,
will return no rows.
In either of the above cases, the correct results are returned
from the query.
4 – DECODE
The DECODE function compares an expression to each supplied
search value until a match is found. When a match is found,
DECODE returns the result in the corresponding result field. If
no match is found, DECODE returns the default if it is specified,
null if no default is specified.
Example: Using the DECODE function
SQL> SELECT employee_id, last_name, first_name,
cont> DECODE (status_code, '1', 'Full time',
cont> '2', 'Part time')
cont> FROM employees
cont> LIMIT TO 5 ROWS;
EMPLOYEE_ID LAST_NAME FIRST_NAME
00165 Smith Terry Part time
00190 O'Sullivan Rick Full time
00187 Lasch Stan Full time
00169 Gray Susan Full time
00176 Hastings Norman Full time
5 rows selected
5 – GREATEST and LEAST Functions
The GREATEST and LEAST functions accept a list of two or more
value expressions (all of which must be of comparable types) and
return the greatest value from the list for the GREATEST function
and the least value from the list for the LEAST function.
The value expressions specified can be column references,
subselects, function calls, literal values, and other complex
value expressions.
The data type of the resulting expression is a common data type
to which all value expressions in the list can be converted. For
example, LEAST(10, 10.3, 123E100) results in a DOUBLE PRECISION
result because at least one literal is DOUBLE PRECISION.
If the result data type resolves to a fixed CHARACTER string,
then GREATEST and LEAST return a CHARACTER VARYING (also known as
VARCHAR) string with the maximum length.
The NULL keyword can appear in the list but is ignored. However,
not all value expressions can be specified as NULL. That is, a
non-NULL value expression must be in the list so that the data
type for the expression can be determined.
The GREATEST and LEAST functions can result in NULL only if at
run time all value expressions result in NULL.
If DATE VMS literals are used as an argument to the GREATEST or
LEAST function, the date string must be prefixed with the type
DATE VMS so that SQL will accept it as a DATE VMS literal and not
a string literal. See the following examples.
Examples: Using the GREATEST and LEAST functions
Example 1 Using CHARACTER Versus DATE VMS Literals
SQL> -- Different results are returned by the LEAST function (and
SQL> -- the GREATEST function) if the parameters are treated as
SQL> -- CHARACTER or DATE VMS literals. This is because it is
SQL> -- the data types of the source expressions that determine the
SQL> -- the result data type of the LEAST (and GREATEST) functions.
SQL> select LEAST ('1-APR-2001', '10-JAN-2000'),
cont> LEAST (DATE VMS'1-APR-2001', DATE VMS'10-JAN-2000')
cont> from rdb$database;
1-APR-2001 10-JAN-2000 00:00:00.00
1 row selected
Example 2 finds the least value for the birthday of employees
from two employees tables.
Example 2 Using the LEAST Function
SQL> SELECT LEAST (M.BIRTHDAY, E.BIRTHDAY, :SUPPLIED_DATE)
cont> FROM EMPLOYEES E, JOB_HISTORY JH, DEPARTMENTS D, EMPLOYEES M
cont> WHERE E.EMPLOYEE_ID = JH.EMPLOYEE_ID AND
cont> ...
6 – NULLIF Expressions
The NULLIF expression is used to substitute NULL when two value
expressions are equal. For example, if the data stored in column
ADDRESS_DATA_1 or ADDRESS_DATA_2 are space characters, the NULLIF
expression replaces the space value with the NULL value.
SQL> BEGIN
cont> INSERT INTO EMPLOYEES
cont> (EMPLOYEE_ID, LAST_NAME, FIRST_NAME,
cont> ADDRESS_DATA_1, ADDRESS_DATA_2)
cont> VALUES
cont> (:EMP_ID, 'Clinton', 'William',
cont> NULLIF(:ADD_1, ' '),
cont> NULLIF(:ADD_2, ' '));
cont> END;
SQL>
SQL> SELECT LAST_NAME, ADDRESS_DATA_1, ADDRESS_DATA_2
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID = :EMP_ID;
LAST_NAME ADDRESS_DATA_1 ADDRESS_DATA_2
Clinton NULL NULL
1 row selected
The following example substitutes NULL when the MIDDLE_INITIAL
column of the EMPLOYEES table contains space characters:
SQL> SELECT LAST_NAME,
cont> NULLIF (MIDDLE_INITIAL, ' '),
cont> FIRST_NAME
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID IN ('00191', '00198');
LAST_NAME FIRST_NAME
Pfeiffer I Karen
Gehr NULL Leslie
2 rows selected
7 – NVL Expressions
substitutes NULL when two value expressions are equal; otherwise,
returns NULL. The NVL expression is a synonym for the COALESCE
expression. See the COALESCE_Expressions HELP topic for more
information.
8 – NVL2 Expressions
NVL2 lets you compute a value based on whether a specified
expression is null or not null. If the first value expression
is not null then the second value expression is returned as
the function result. Otherwise, the final value expression is
returned. The data type function is derived as a common data type
of the second and third value expressions.
For example, when the JOB_END date in JOB_HISTORY is NULL then
that indicates the current job for that employee. The following
example uses NVL2 to annotate the output from a query on JOB_
HISTORY displaying either "current job" or "prior job" based on
the NULL attribute of the JOB_END column.
SQL> select employee_id, job_start, job_end,
cont> NVL2 (job_end, 'prior job', 'current job')
cont> from job_history
cont> where employee_id < '00180'
cont> order by employee_id, job_start;
EMPLOYEE_ID JOB_START JOB_END
00164 5-Jul-1980 20-Sep-1981 prior job
00164 21-Sep-1981 NULL current job
00165 1-Jul-1975 4-Sep-1977 prior job
00165 5-Sep-1977 7-Apr-1979 prior job
00165 8-Apr-1979 7-Mar-1981 prior job
00165 8-Mar-1981 NULL current job
.
.
.
The following example shows whether the income of some employees
is made up of SALARY plus COMMISSION, or just SALARY, depending
on whether the COMMISSION_PCT column of EMPLOYEES is null or not.
SQL> SELECT last_name, salary_amount,
cont> NVL2 (commission_pct,
cont> salary_amount + (salary_amount * commission_pct),
cont> salary_amount) as Income edit using SALARY
cont> FROM employees e, salary_history sh
cont> WHERE last_name like 'B%'
cont> and e.employee_id = sh.employee_id
cont> and salary_end is null
cont> ORDER BY last_name;
E.LAST_NAME SH.SALARY_AMOUNT INCOME
Babbin $20,150.00 $20,956.00
Bartlett $14,817.00 $15,261.51
Bartlett $38,223.00 $38,987.46
Belliveau $54,649.00 $55,741.98
Blount $63,080.00 $64,341.60
Boyd $30,275.00 $30,275.00
Boyd $24,166.00 $24,166.00
Brown $50,357.00 $50,357.00
Burton $23,053.00 $23,053.00
9 rows selected
SQL>
9 – SIGN Function
SIGN returns an INTEGER value. SIGN accepts any numeric (fixed or
floating) or interval value expression. If the value expression
evaluates to NULL, then the SIGN function returns NULL. If the
value expression evaluates to a negative value, then SIGN returns
-1; if the value is positive then SIGN returns 1; otherwise a
zero will be returned.
Example: Using SIGN Builtin Function
This example computes delayed departures from the LAYOVER table.
SQL> select arr_date,
cont> dep_date,
cont> DECODE (SIGN ((dep_date - arr_date) day(9)),
cont> -1, 'date error - can not depart before arrival',
cont> 0, 'same day departure',
cont> 1, 'delayed')
cont> from LAYOVER;
2005-12-22 2006-01-20 delayed
2005-12-23 2005-12-25 delayed
2006-01-30 2006-02-01 delayed
2006-02-06 2006-02-09 delayed
2006-01-24 2006-01-26 delayed
2006-02-02 2006-02-19 delayed
2007-02-10 2007-02-16 delayed
2007-02-20 2007-02-26 delayed
2007-05-29 2007-06-08 delayed
2007-06-12 2007-06-26 delayed
2007-05-15 2007-05-21 delayed
2007-09-10 2007-09-14 delayed
2007-09-04 2007-09-06 delayed
2007-09-19 2007-09-20 delayed
2007-09-21 2007-09-24 delayed
15 rows selected