Supports conditional SQL expressions; can be used anywhere a value expression can be used.
case when search_condition then expression [when search_condition then expression]... [else expression] end
case expression simplifies standard SQL expressions by allowing you to express a search condition using a when...then construct instead of an if statement.
case expressions can be used anywhere an expression can be used in SQL.
If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy. If you specify two datatypes that Adaptive Server cannot implicitly convert (for example, char and int), the query fails.
The ability to handle case expressions is set for Adaptive Server 11.5 and later. The presence of a case expression in the original query syntax does not cause the query optimizer to reject quickpass mode.
The ability to handle case expressions is set for ASA 6.0, ASIQ 12.0, and all later versions. The presence of a case expression in the original query syntax will not cause the query optimizer to reject quickpass mode.
The ability to handle case expressions is not set for servers in this class. When a SQL statement containing a case expression is optimized, the presence of the case expression causes the Component Integration Services quickpass optimization to reject the statement. When this happens, the case expression must be evaluated by the local Adaptive Server after retrieving data from the remote server.
The ability to handle case expressions is determined by the result set from the RPC sp_capabilities. If direct_connect indicates that it can handle case expressions, then Component Integration Services forwards them to the direct_connect when quickpass mode is used to handle the query.
The ability to handle case expressions is, by default, not set for servers in this class. When a SQL statement containing a case expression is optimized, the presence of the case expression causes Component Integration Services quickpass optimization to reject the statement. When this happens, the case expression must be evaluated by the local Adaptive Server after retrieving data from the remote server.
If trace flag 11215 is turned on, the default capabilities for server class db2 are modified to enable more capabilities, and case expressions are enabled by default. DB2 does recognize case expression syntax. Also, the trace flag must be turned on before Component Integration Services makes its first connection to the remote server, or else the capabilities set by the first connection remain in effect until the server is restarted.
