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
Usage
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 capability for handling case expressions is set for ASE 11.5 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 capability for handling 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 capability for handling 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 will cause CIS quickpass optimization to reject the statement. When this happens, the case expression must be evaluated by the local ASE after retrieving data from the remote server.
The capability for handling 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 will cause CIS quickpass optimization to reject the statement. When this happens, the case expression must be evaluated by the local ASE after retrieving data from the remote server.
The capability for handling case expressions is determined by the result set from the RPC sp_capabilities. If the direct_connect indicates that it can handle case expressions, then CIS will forward them to the direct_connect when quickpass mode is used to handle the query.
The capability for handling 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 will cause CIS quickpass optimization to reject the statement. When this happens, the case expression must be evaluated by the local ASE after retrieving data from the remote server.
If traceflag 11215 is turned on, the default capabilities for server class db2 are modified to enable more capabilities, and case expressions are enabled by default. Note that DB2 does recognize case expression syntax. Also, the traceflag must be turned on before CIS makes its first connection to the remote server, or else the capabilities set by the first connection will remain in effect until the server is rebooted.
