Execution order of WHEN clauses in a CASE statement

The value that is returned will be the value of the THEN expression for the earliest WHEN clause (textually) that matches. That does mean that if your line 2 conditions are met, the result will be A2.

But, if your THEN expressions were more complex than just literal values, some of the work to evaluate those expressions may happen even when that expression is not required.

E.g.

 WHEN r.code="00"                        then 'A1'
 WHEN r.code="01" AND r.source="PXWeb" then 'A2'
 WHEN r.code="0120"                      then 1/0
 WHEN r.code="01"                        then 'A4'

could generate a division by zero error even if r.code isn’t equal to 0120, and even if it’s equal to 00, say. I don’t know what the standard has to say on this particular issue but I know that it is true of some products.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)