CASE (SQL-92 Compatible)
Syntax
case-expr::
searched-case-expr | simple-case-expr
searched-case-expr::
CASE
WHEN search_condition THEN { result-expr | NULL }
[ ... ]
[ ELSE expr | NULL ]
END
simple-case-expr::
CASE primary-expr
WHEN expr THEN { result-expr | NULL }
[ ... ]
[ ELSE expr | NULL ]
END
Description
The CASE scalar function is a type of conditional expression. (See Conditional Expressions for more details and a summary of all the conditional expressions.)
The general form of the CASE scalar function specifies a series of search conditions and associated result expressions. It is called a searched case expression. c-treeSQL returns the value specified by the first result expression whose associated search condition evaluates as true. If none of the search conditions evaluate as true, the CASE expression returns a null value (or the value of some other default expression if the CASE expression includes the ELSE clause).
CASE also supports syntax for a shorthand notation, called a simple case expression, for evaluating whether one expression is equal to a series of other expressions.
Notes
- This function is not allowed in a GROUP BY clause
- Arguments to this function cannot be query expressions
Arguments
CASE
The CASE keyword alone, not followed by primary-expr, specifies a searched case expression. It must be followed by one or more WHEN-THEN clauses each that specify a search condition and corresponding expression.
WHEN search_condition THEN { result-expr | NULL }
WHEN clause for searched case expressions. c-treeSQL evaluates search condition. If search_condition evaluates as true, CASE returns the value specified by result-expr (or null, if the clause specifies THEN NULL).
If search_condition evaluates as false, FairCom DB SQL evaluates the next WHEN-THEN clause, if any, or the ELSE clause, if it is specified.
CASE primary-expr
The CASE keyword followed by an expression specifies a simple case expression. In a simple case expression, one or more WHEN-THEN clauses specify two expressions.
A simple case expression can always be expressed as a searched case expression. Consider the following general simple case expression:
CASE primary-expr
WHEN expr1 THEN result-expr1
WHEN expr2 THEN result-expr2
ELSE expr3
END
The preceding simple case expression is equivalent to the following searched case expression:
CASE
WHEN primary-expr = expr1 THEN result-expr1
WHEN primary-expr = expr2 THEN result-expr2
ELSE expr3
END
WHEN expr THEN { result-expr | NULL }
WHEN clause for simple case expressions. c-treeSQL evaluates expr and compares it with primary-expr specified in the CASE clause. If they are equal, CASE returns the value specified by result-expr (or null, if the clause specifies THEN NULL).
If expr is not equal to primary-expr, c-treeSQL evaluates the next WHEN-THEN clause, if any, or the ELSE clause, if it is specified.
[ ELSE { expr | NULL } ]
In both searched case expressions and simple case expressions, the ELSE clause specifies an optional expression whose value FairCom DB SQL returns if none of the conditions specified in WHEN-THEN clauses were satisfied. If the CASE expression omits the ELSE clause, it is the same as specifying ELSE NULL.
Examples
The following example shows a searched case expression that assigns a label denoting tables as system tables if they begin with the letters sys. Note that this example cannot be reformulated as a simple case expression, since it specifies a relational operator other than =.
SELECT tbl,
CASE
WHEN tbl like 'sys%' THEN 'System Table'
ELSE 'Not System table'
END
FROM systables;
TBL SEARCHED_CASE(TBLSY
--- -------------------
systblspaces System Table
systables System Table
syscolumns System Table
sysindexes System Table
sysdbauth System Table
systabauth System Table
syscolauth System Table
sysviews System Table
syssynonyms System Table
sysdblinks System Table
sys_keycol_usage System Table
sys_ref_constrs System Table
sys_chk_constrs System Table
sys_tbl_constrs System Table
sys_chkcol_usage System Table
sysdatatypes System Table
syscalctable System Table
systblstat System Table
The following example shows a searched CASE expression and an equivalent simple CASE expression.
- Searched case expression:
SELECT tbl,
CASE
WHEN tbltype = 'S' THEN 'System Table'
ELSE 'Not System table'
End
FROM systables;
- Equivalent simple case expression:
SELECT tbl,
CASE tbltype
WHEN 'S' THEN 'System Table'
ELSE 'Not System table'
END
FROM systables;
COALESCE (SQL-92 compatible)
Syntax
COALESCE ( expression1, expression2 [ , ... ] )
Description
The COALESCE scalar function is a type of conditional expression. (See Conditional Expressions for more information and a summary of all the conditional expressions.)
COALESCE specifies a series of expressions, and returns the first expression whose value is not null. If all the expressions evaluate as null, COALESCE returns a null value.
The COALESCE syntax is shorthand notation for a common case that can also be represented in a CASE expression. The following two formulations are equivalent:
COALESCE ( expression1 , expression2 , expression3 )
CASE
WHEN expression1 IS NOT NULL THEN expression1
WHEN expression2 IS NOT NULL THEN expression2
ELSE expression3
Example
SELECT COALESCE(end_date, start_date) from job_hist;
Notes
- This function is not allowed in a GROUP BY clause
- Arguments to this function cannot be query expressions
DECODE function (extension)
Syntax
DECODE ( expression, search_expression, match_expression
[ , search_expression, match_expression ...]
[ , default_expression ] )
Description
The DECODE scalar function is a type of conditional expression. (Refer to Conditional Expressions for a summary of all the conditional expressions.)
The scalar function DECODE compares the value of the first argument expression with each search_expression and if a match is found, returns the corresponding match_expression. If no match is found, then the function returns default_expression. If default_expression is not specified and no match is found, the function returns a null value.
DECODE provides a subset of the functionality of CASE that is compatible with Oracle SQL syntax. Use a simple case expression for SQL-compatible syntax (See CASE (SQL-92 Compatible)).
Example
SELECT ename, DECODE (deptno,
10, 'ACCOUNTS ',
20, 'RESEARCH ',
30, 'SALES ',
40, 'SUPPORT ',
'NOT ASSIGNED'
)
FROM employee ;
Notes
- The first argument expression can be of any type. The types of all search_expressions must be compatible with the type of the first argument.
- The match_expressions can be of any type. The types of all match_expressions must be compatible with the type of the first match_expression.
- The type of the default_expression must be compatible with the type of the first match_expression.
- The type of the result is the same as that of the first match_expression.
- If the first argument expression is null then the value of the default_expression is returned, if it is specified. Otherwise null is returned.
GREATEST function (extension)
Syntax
GREATEST ( expression, expression, ... )
Description
The scalar function GREATEST returns the greatest value among the values of the given expressions.
Example
SELECT cust_no, name,
GREATEST (ADD_MONTHS (start_date, 10), SYSDATE)
FROM customer ;
Notes
- The first argument to the function can be of any type. The types of the subsequent arguments must be compatible with that of the first argument.
- The type of the result is the same as that of the first argument.
- If any of the argument expressions evaluates to null, the result is null.
IFNULL function (ODBC compatible)
Syntax
IFNULL( expr, value)
Description
The scalar function IFNULL returns value if expr is null. If expr is not null, IFNULL returns expr.
Example
select c1, ifnull(c1, 9999) from temp order by c1;
c1 ifnull(c1,9999)
9999
9999
9999
1 1
3 3
Notes
The data type of value must be compatible with the data type of expr.
ISNULL function
Syntax
ISNULL ( expr, value )
Description
This scalar function is the same as the scalar function IFNULL. Refer to IFNULL for usage.
LEAST function (extension)
Syntax
LEAST ( expression, expression, ... )
Description
The scalar function LEAST returns the lowest value among the values of the given expressions.
Example
SELECT cust_no, name,
LEAST (ADD_MONTHS (start_date, 10), SYSDATE)
FROM customer ;
Notes
- The first argument to the function can be of any type. The types of the subsequent arguments must be compatible with that of the first argument.
- The type of the result is the same as that of the first argument.
- If any of the argument expressions evaluates to null, the result is null.
NVL function (extension)
Syntax
NVL ( expression, expression )
Description
The scalar function NVL returns the value of the first expression if the first expression value is not null. If the first expression value is null, the value of the second expression is returned.
The NVL function is not ODBC compatible. Use the IFNULL function for ODBC-compatible syntax.
Example
SELECT salary + NVL (comm, 0) 'TOTAL SALARY'
FROM employee ;
Notes
- The first argument to the function can be of any type except for LONG data types.
- The type of the second argument must be compatible with that of the first argument.
- The type of the result is the same as the first argument.
NULLIF (SQL-92 compatible)
Syntax
NULLIF ( expression1, expression2 )
Description
The NULLIF scalar function is a type of conditional expression (See Conditional Expressions for more information and a summary of all the conditional expressions).
The NULLIF scalar function returns a null value for expression1 if it is equal to expression2. It’s useful for converting values to null from applications that use some other representation for missing or unknown data.
Notes
- This function is not allowed in a GROUP BY clause.
- Arguments to this function cannot be query expressions.
- The NULLIF expression is shorthand notation for a common case that can also be represented in a CASE expression, as follows:
CASE
WHEN expression1 = expression2 THEN NULL
ELSE expression1
Example
This example uses the NULLIF scalar function to insert a null value into an address column if the host-language variable contains a single space character.
INSERT INTO employee (add1) VALUES (NULLIF (:address1, ' '));