Logical functions

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, ' '));