ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Operands and Expressions → ABAP SQL - SQL Expressions sql_exp → sql_exp - sql_case
sql_exp - sql_searched_case
7.31 | 7.40 | 7.54
... CASE WHEN sql_cond1 THEN result1
[WHEN sql_cond2 THEN result2]
[WHEN sql_cond3 THEN result3]
Complex case distinction (searched case) in ABAP SQL. This
SQL expression evaluates logical expressions
sql_cond2, ... and produces
result as a result after the first
for which the logical expression is true. If none of the logical expressions are true, the
ELSE is selected. If
ELSE is not specified, the result is the
zero value. The results
result2, ... can be any
The results must be compatible with each other in such a way that a common result type can be determined:
The data types
result2, ... must be either
the same or the data type must be able to fully represent the value of all other data types. If this
is not the case, a statically specified type raises a syntax error and a dynamically specified type
raises an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS. The result has the dictionary type of the entry with the greatest value range.
If a complex
CASE is used in a
condition of an ABAP SQL statement, the
client column of a client-specific
data source of a query or of the target of a write statement cannot be used as the operand of a
WHEN condition due to
implicit client handling.
- The SQL standard dictates the result of a case distinction, but not the order in which the operands are evaluated. Potentially, the result may even be evaluated before the associated condition. This means that any expressions specified as operands must have no side effects and must not be dependent on each other.
- On the SAP HANA database, operands are evaluated in parallel for reasons of optimization. The order in which the operands are evaluated is undefined. If an exception is raised when am operand is evaluated, the entire case distinction is canceled. Here, it does not matter which conditions apply and the order in which they are noted. From this reason, it is advisable not to use any exceptions in expressions specified as operands. More information can be found in the HANA-specific SQL documentation.
- If the case distinction is evaluated in the table buffer, the order of processing is preserved and there is no crash when an operand is evaluated whose condition is not true.
- The relational expressions that can be used after
CASEare a subset of the relational expressions for statements, but also allow SQL expressions as operands on the right side.
- When complex
CASEis used, the syntax check is performed in a strict mode from Release 7.40, SP08, which handles the statement more strictly than the regular syntax check.
- A client column can be used as an operand only if
implicit client handling is disabled using the obsolete addition
CLIENT SPECIFIEDfor the query or the write statement.
- When SQL expressions are specified on the left side or host expressions occur on the right side, the syntax check is performed in a strict mode from Release 7.50, which handles the statement more strictly than the regular syntax check.
Evaluates the column FLTIME in a complex
SELECT FROM spfli FIELDS carrid, connid, cityfrom, cityto, CASE WHEN fltime < 100 THEN 'short' WHEN fltime BETWEEN 100 AND 300 THEN 'medium' ELSE 'long' END AS fltime ORDER BY carrid, connid INTO TABLE @DATA(result). cl_demo_output=>display( result ).