This is an old revision of the document!


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

Additional Resources

See also:

DRAFT

Omnidex SQL Function: CASE

Description

The CASE function handles simple IF - THEN - ELSE logic at a select-item level. The WHEN conditions are evaluated in order until a condition proves TRUE, at which point the THEN expression is executed. All of the THEN expressions in a CASE statement must be of the same data type: numeric, character, or date.

CASE supports multiple conditions per case.

Syntax

CASE
    WHEN condition_expression THEN scalar_expression
    ELSE scalar_expression
END

or

CASE scalar_expression_1
    WHEN scalar_expression_2 THEN scalar_expression_3
    ELSE scalar_expression_4
END

Example

Example 1 Condition Expression

SELECT ...
  CASE
  WHEN PRODUCTS.CATEGORY =30 THEN 'Furniture' 
  WHEN PRODUCTS.CATEGORY = 70 THEN 'Machines'
  WHEN PRODUCTS.CATEGORY = 90 THEN 'Paper Products' 
  ELSE 'Office Supplies'
  END;

Example 2 Scalar Expression

  CASE DATES.MO 
  WHEN 'Jan' THEN 1 
  WHEN 'Feb' THEN 2 
  WHEN 'Mar' THEN 3 
  WHEN 'Apr' THEN 4 
  ELSE 0 
  END

Example 3 Select Statement with Conditional Expression

SELECT company,
 
CASE 
WHEN customers.state='CA' THEN 'CALIFORNIA' 
WHEN customers.state='CO' THEN 'COLORADO' 
WHEN customers.state='NY' THEN 'NEW YORK' 
WHEN customers.state='NJ' THEN 'NEW JERSEY' 
ELSE 'OTHER' 
END
 
FROM customers 
WHERE state IN ('ca','co','ny','nj','az','fl','nv') 
ORDER BY state;

Example 4 Select Statement with Scalar Expression

SELECT company, contact,
 
CASE customers.state 
WHEN 'AZ' THEN 'ARIZONA' 
WHEN 'FL' THEN 'FLORIDA' 
WHEN 'NV' THEN 'NEVADA' 
ELSE 'OTHER' 
END
 
FROM customers 
WHERE company='systems' 
ORDER BY state
 
Back to top
dev/sql/functions/case.1280261130.txt.gz · Last modified: 2016/06/28 22:38 (external edit)