This is an old revision of the document!
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 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
CASE WHEN PRODUCTS.CATEGORY < 30 THEN 'Furniture' WHEN PRODUCTS.CATEGORY < 70 THEN 'Machines' WHEN PRODUCTS.CATEGORY < 90 THEN 'Paper Products' ELSE 'Office Supplies' END
CASE DATES.MO WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 ELSE 0 END
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
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