ASA SQL Reference
SQL Language Elements
Expressions
The CASE expression provides conditional SQL expressions. Case expressions can be used anywhere an expression can be used.
The syntax of the CASE expression is as follows:
CASE expression
WHEN expression
THEN expression, ...
[ ELSE expression ]
END
If the expression following the CASE statement is equal to the expression following the WHEN statement, then the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is returned, if it exists.
For example, the following code uses a case expression as the second clause in a SELECT statement.
SELECT id, ( CASE name WHEN 'Tee Shirt' then 'Shirt' WHEN 'Sweatshirt' then 'Shirt' WHEN 'Baseball Cap' then 'Hat' ELSE 'Unknown' END ) as Type FROM "DBA".Product
An alternative syntax is as follows:
CASE
WHEN search-condition
THEN expression, ...
[ ELSE expression ]
END
If the search-condition following the WHEN statement is satisfied, the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is returned, if it exists.
For example, the following statement uses a case expression as the third clause of a SELECT statement to associate a string with a search-condition.
SELECT id, name, ( CASE WHEN name='Tee Shirt' then 'Sale' WHEN quantity >= 50 then 'Big Sale' ELSE 'Regular price' END ) as Type FROM "DBA".Product
The NULLIF function provides a way to write some CASE statements in short form. The syntax for NULLIF is as follows:
NULLIF ( expression-1, expression-2 )
NULLIF compares the values of the two expressions. If the first expression equals the second expression, NULLIF returns NULL. If the first expression does not equal the second expression, NULLIF returns the first expression.
CASE statement is different from CASE expressionDo not confuse the syntax of the CASE expression with that of the CASE statement.For information on the CASE statement, see CASE statement. |