Expressions that reference the rand function, the getdate function, and so on, produce different values each time they are evaluated. This can yield unexpected results when you use these expressions in certain case expressions.

For example, the SQL standard specifies that case expressions with this
form:

case expression when value1 then result1 when value2 then result2 when value3 then result3 ... end

are equivalent to this form of case expression:

case expression when expression=value1 then result1 when expression=value2 then result2 when expression=value3 then result3 ... end

This definition explicitly requires that the expression be evaluated repeatedly in each when clause that is examined. This definition of case expressions affects case expressions that reference functions such as the rand function. For example, this case expression:

select CASE convert(int, (RAND() * 3)) when 0 then "A" when 1 then "B" when 2 then "C" when 3 then "D" else "E" end

is defined to be equivalent to this one, according to the SQL standard:

select CASE when convert(int, (RAND() * 3)) = 0 then "A" when convert(int, (RAND() * 3)) = 1 then "B" when convert(int, (RAND() * 3)) = 2 then "C" when convert(int, (RAND() * 3)) = 3 then "D" else "E" end

In this form, a new rand value is generated for each when clause, and the case expression frequently produces the result āEā.

Created May 28, 2014. Send feedback on this help topic to Technical Publications: pubs@sap.com