This is an old revision of the document!
The CASE function performs simple IF … THEN … ELSE … END logic within a function. Typically this is used to set a value based on a set of different conditions. The conditions are evaluated in the order they appear in the function until a condition proves TRUE, at which point the THEN expression is executed. If none of the conditions prove TRUE, then the final ELSE expression is executed.
This functions returns the same datatype as the THEN clauses in the function. All of the THEN expressions in a CASE statement must be of the same datatype.
The CASE function supports two standard syntaxes:
CASE WHEN condition_expression_1 THEN scalar_expression_1 WHEN condition_expression_2 THEN scalar_expression_2 ELSE scalar_expression END
and
CASE scalar_expression_1 WHEN scalar_expression_2 THEN scalar_expression_3 WHEN scalar_expression_4 THEN scalar_expression_5 ELSE scalar_expression_6 END
Required. A conditional expression using SQL language that must prove TRUE in order for the function to return the corresponding scalar expression. A conditional expression is generally equivalent to criteria used in a WHERE clause of a SQL SELECT statement, such as “state in ('CA','OR','WA')” or “birthdate >= 'Jan 1, 1980'.
Required. An expression using SQL language that produces a value. A scalar expression is generally equivalent to a select item in the SQL SELECT statement, and can be a character expression, numeric expression or date expression.
> select name, >> gender, >> case gender >> when 'M' then 'Male' >> when 'F' then 'Female' >> else 'Did not specify' >> end gender_desc >> from individuals; NAME G GENDER_DESC -------------------------------------------------- - --------------- MS. MARY LEWIS F Female MRS. ALICE R TOLLIVER F Female MR. SIDNEY K TOLLIVER M Male MRS. JENNIFER J HOPKINS F Female MR. CHARLIE HOPKINS M Male MS. KIMBERLY BLUE F Female KATHLEEN TUCKER F Female
> select name, >> gender, >> birthdate, >> case >> when gender = 'M' and $compare_dates(birthdate, current_date, 'YY') < 18 >> then 'Mstr.' >> when gender = 'M' and $compare_dates(birthdate, current_date, 'YY') >= 18 >> then 'Mr.' >> when gender = 'F' and $compare_dates(birthdate, current_date, 'YY') < 18 >> then 'Miss' >> when gender = 'F' and $compare_dates(birthdate, current_date, 'YY') >= 18 >> then 'Ms.' >> else >> '' >> end salutation >> from individuals; NAME G BIRTHDATE SALUT -------------------------------------------------- - ------------ ----- MS. MARY LEWIS F 1938-07-14 Ms. MRS. ALICE R TOLLIVER F 1957-11-22 Ms. MR. SIDNEY K TOLLIVER M 1960-07-28 Mr. MRS. JENNIFER J HOPKINS F 1929-08-22 Ms. MR. CHARLIE HOPKINS M 1933-07-09 Mr. MS. KIMBERLY BLUE F 1944-08-01 Ms. KATHLEEN TUCKER F 2004-09-14 Miss
</code>
See also:
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