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-1 WHEN expression-2 THEN expression-3, ... [ ELSE expression-4 ] { END | END CASE }
If the expression following the CASE clause is equal to the expression following the WHEN clause, then the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is returned, if it exists.
the CASE expression returns NULL if the ELSE clause doesn't exist and expression-1' doesn't match any of the expression-2...expression-n values.
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 Products; |
An alternative syntax is as follows:
CASE WHEN search-condition THEN expression-1, ... [ ELSE expression-2 ] END [ CASE ]
If the search-condition following the WHEN clause 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 Products; |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |