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 supports multiple conditions per case.
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
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;
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