Show pagesource

Old revisions

QuickLinks	Statements	Functions	OdxSQL

Omnidex SQL Function: CASE

Description

The CASE function performs simple IF … THEN … ELSE … END logic within a function. Typically this is used to set a value based on a set of different conditions. The conditions are evaluated in the order they appear in the function until a condition proves TRUE, at which point the THEN expression is executed. If none of the conditions prove TRUE, then the final ELSE expression is executed.

This functions returns the same datatype as the THEN clauses in the function. All of the THEN expressions in a CASE statement must be of the same datatype.

Syntax

The CASE function supports two standard syntaxes:

CASE
    WHEN conditional_expression_1 THEN scalar_expression_1
    WHEN conditional_expression_2 THEN scalar_expression_2
    ELSE scalar_expression
END

and

CASE scalar_expression_1
    WHEN scalar_expression_2 THEN scalar_expression_3
    WHEN scalar_expression_4 THEN scalar_expression_5
    ELSE scalar_expression_6
END

conditional_expression

Required. A conditional expression using SQL language that must prove TRUE in order for the function to return the corresponding scalar expression. A conditional expression is generally equivalent to criteria used in a WHERE clause of a SQL SELECT statement, such as “state in ('CA','OR','WA')” or “birthdate >= 'Jan 1, 1980'.

scalar_expression

Required. An expression using SQL language that produces a value. A scalar expression is generally equivalent to a select item in the SQL SELECT statement, and can be a character expression, numeric expression or date expression.

Example

Example 1: CASE against column values

> select name,
>>        gender,
>>        case gender
>>          when 'M' then 'Male'
>>          when 'F' then 'Female'
>>          else          'Did not specify'
>>          end gender_desc
>>   from individuals;

NAME                                                G  GENDER_DESC
--------------------------------------------------  -  ---------------
MS. MARY LEWIS                                      F  Female
MRS. ALICE R TOLLIVER                               F  Female
MR. SIDNEY K TOLLIVER                               M  Male
MRS. JENNIFER J HOPKINS                             F  Female
MR. CHARLIE HOPKINS                                 M  Male
MS. KIMBERLY BLUE                                   F  Female
KATHLEEN TUCKER                                     F  Female

Example 2: CASE using complex expressions

> select name, 
>>       gender,
>>       birthdate,
>>       case 
>>         when gender = 'M' and $compare_dates(birthdate, current_date, 'YY') < 18
>>           then 'Mstr.'
>>         when gender = 'M' and $compare_dates(birthdate, current_date, 'YY') >= 18
>>           then 'Mr.'
>>         when gender = 'F' and $compare_dates(birthdate, current_date, 'YY') < 18
>>           then 'Miss'
>>         when gender = 'F' and $compare_dates(birthdate, current_date, 'YY') >= 18
>>           then 'Ms.' 
>>         else
>>                ''
>>         end salutation
>>  from individuals;

NAME                                                G  BIRTHDATE     SALUT
--------------------------------------------------  -  ------------  -----
MS. MARY LEWIS                                      F    1938-07-14  Ms.
MRS. ALICE R TOLLIVER                               F    1957-11-22  Ms.
MR. SIDNEY K TOLLIVER                               M    1960-07-28  Mr.
MRS. JENNIFER J HOPKINS                             F    1929-08-22  Ms.
MR. CHARLIE HOPKINS                                 M    1933-07-09  Mr.
MS. KIMBERLY BLUE                                   F    1944-08-01  Ms.
KATHLEEN TUCKER                                     F    2004-09-14  Miss