Execution order of WHEN clauses in a CASE statement

Question

Given the following body of a case statement:

1    WHEN r.code= '00'                        then 'A1' 2    WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'   < 3    WHEN r.code ='0120'                      then 'A3' 4    WHEN r.code ='01'                        then 'A4'   < 5    WHEN r.code ='1560'                      then 'A5' 6    WHEN r.code ='1530'                      then 'A6' 7    WHEN r.code ='1550'                      then 'A7' 

I'm assuming line 2 will always execute before line 4? Then I read statements like 'SQL is a declarative language, meaning that it tells the SQL engine what to do, not how' in

Order Of Execution of the SQL query

and wonder if this also relates to the order of execution in the CASE statement. Essentially, can i leave the code above as it is without having to change line 4 to

4    WHEN r.code ='01' AND r.source != 'PXWeb' then 'A4'    

Answer 1

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.