Provides conditional SQL expressions.
CASE expression1 WHEN expression2 THEN expression3, ... [ ELSE expression4 ] END
SELECT id, ( CASE name WHEN 'Tee Shirt' THEN 'Shirt' WHEN 'Sweatshirt' THEN 'Shirt' WHEN 'Baseball Cap' THEN 'Hat' ELSE 'Unknown' END ) as Type FROM Product;
CASE WHEN search-condition THEN expression1, ... [ ELSE expression2 ] END
For compatibility reasons, you can end this expression with either ENDCASE or END CASE.
You can use case expressions anywhere you can use regular expression.
Syntax 1 If the expression following the CASE keyword is equal to the expression following the first WHEN keyword, then the expression following the associated THEN keyword is returned. Otherwise the expression following the ELSE keyword is returned, if specified.
For example, the following code uses a case expression as the second clause in a SELECT statement. It selects a row from the Product table where the name column has a value of Sweatshirt.
Syntax 2 If the search-condition following the first WHEN keyword is TRUE, the expression following the associate THEN keyword is returned. Otherwise the expression following the ELSE clause is returned, if specified.
NULLIF function for abbreviated CASE expressions 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.
The following statement uses a CASE expression as the third clause of a SELECT statement to associate a string with a search condition. If the name column's value is Tee Shirt, this query returns Sale. And if the name column's value is not Tee Shirt and the quantity is greater than fifty, it returns Big Sale. However, for all others, the query then returns Regular price.
SELECT id, name, ( CASE WHEN name='Tee Shirt' THEN 'Sale' WHEN quantity >= 50 THEN 'Big Sale' ELSE 'Regular price' END ) as Type FROM Product;
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|