This is an old revision of the document!


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.

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

  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.1259965620.txt.gz ยท Last modified: 2016/06/28 22:38 (external edit)