Menu
What is the complete and correct syntax for the SQL Case expression?
811
The SQL CASE Expression The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.
CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component. You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN . This includes stringing together multiple conditional statements using AND and OR .
The complete syntax depends on the database engine you're working with:
For SQL Server:
CASE case-expression WHEN when-expression-1 THEN value-1 [ WHEN when-expression-n THEN value-n ... ] [ ELSE else-value ] END or:
CASE WHEN boolean-when-expression-1 THEN value-1 [ WHEN boolean-when-expression-n THEN value-n ... ] [ ELSE else-value ] END expressions, etc:
case-expression - something that produces a value when-expression-x - something that is compared against the case-expression value-1 - the result of the CASE statement if: the when-expression == case-expression OR the boolean-when-expression == TRUE boolean-when-exp.. - something that produces a TRUE/FALSE answer Link: CASE (Transact-SQL)
Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.
Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL.
157
Considering you tagged multiple products, I'd say the full correct syntax would be the one found in the ISO/ANSI SQL-92 standard:
<case expression> ::= <case abbreviation> | <case specification> <case abbreviation> ::= NULLIF <left paren> <value expression> <comma> <value expression> <right paren> | COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren> <case specification> ::= <simple case> | <searched case> <simple case> ::= CASE <case operand> <simple when clause>... [ <else clause> ] END <searched case> ::= CASE <searched when clause>... [ <else clause> ] END <simple when clause> ::= WHEN <when operand> THEN <result> <searched when clause> ::= WHEN <search condition> THEN <result> <else clause> ::= ELSE <result> <case operand> ::= <value expression> <when operand> ::= <value expression> <result> ::= <result expression> | NULL <result expression> ::= <value expression> Syntax Rules
1) NULLIF (V1, V2) is equivalent to the following <case specification>: CASE WHEN V1=V2 THEN NULL ELSE V1 END 2) COALESCE (V1, V2) is equivalent to the following <case specification>: CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END 3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the following <case specification>: CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n ) END 4) If a <case specification> specifies a <simple case>, then let CO be the <case operand>: a) The data type of each <when operand> WO shall be comparable with the data type of the <case operand>. b) The <case specification> is equivalent to a <searched case> in which each <searched when clause> specifies a <search condition> of the form "CO=WO". 5) At least one <result> in a <case specification> shall specify a <result expression>. 6) If an <else clause> is not specified, then ELSE NULL is im- plicit. 7) The data type of a <case specification> is determined by ap- plying Subclause 9.3, "Set operation result data types", to the data types of all <result expression>s in the <case specifica- tion>. Access Rules None. General Rules 1) Case: a) If a <result> specifies NULL, then its value is the null value. b) If a <result> specifies a <value expression>, then its value is the value of that <value expression>. 2) Case: a) If the <search condition> of some <searched when clause> in a <case specification> is true, then the value of the <case specification> is the value of the <result> of the first (leftmost) <searched when clause> whose <search condition> is true, cast as the data type of the <case specification>. b) If no <search condition> in a <case specification> is true, then the value of the <case expression> is the value of the <result> of the explicit or implicit <else clause>, cast as the data type of the <case specification>. If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
