1 Conditional_Expressions 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 2 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 2 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. 2 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 2 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> ... 2 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 2 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. 2 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> 2 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