Case Expression Restrictions

In addition to the restrictions on value expressions listed in Restrictions on Using a Value Expression, the use of a CASE expression in a select list has the following additional restrictions:

• CASE expressions cannot contain functions that use the DISTINCT keyword.
• All resulting values of the CASE expression must be of the same data type. The resulting data types are not evaluated until the query is executed. Therefore, it is possible that a bad SELECT statement can be created, which later causes an exception to be returned after executing the query.
• CASE expressions that contain subexpressions are parsed, but do not return an error.

Additionally, an internal limit is applied to nested CASE expressions to prevent the exhaustion of resources. This limit is different from the expression depth limit and can vary based on the composition of the query. The limit is applied during the parsing of the nested CASE expression. When the limit is reached, the following error is returned:

Error Code  = Resource limit reached
Description = SQL parser stack overflow