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 data type: 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
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
|
