SQL Functions

CASE

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 datatype: numeric, character, or date.

Syntax

CASE
WHEN condition_expression THEN scalar_expression
ELSE scalar_expression
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

OR

CASE scalar_expression_1
WHEN scalar_expression_2 THEN scalar_expression_3
ELSE scalar_expression_4
END

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

top

Example

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

OR

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

top