Skip to content

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

Other versions: 7.31 | 7.40 | 7.54

Syntax


... CASE WHEN sql_cond1 THEN result1 

        [WHEN sql_cond2 THEN result2] 

        [WHEN sql_cond3 THEN result3] 

          ... 
        [ELSE resultn] 
    END ...

Effect

Complex case distinction (searched case) in ABAP SQL. This SQL expression evaluates logical expressions sql_cond1, sql_cond2, ... and produces the operand result as a result after the first THEN for which the logical expression is true. If none of the logical expressions are true, the result specified after ELSE is selected. If ELSE is not specified, the result is the zero value. The results result1, result2, ... can be any SQL expressions.

The results must be compatible with each other in such a way that a common result type can be determined: The data types result1, 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.


Notes

  • 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.

  • A client column can be used as an operand only if implicit client handling is disabled using the obsolete addition CLIENT SPECIFIED for 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.


Example

Evaluates the column FLTIME in a complex CASE.

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 ).

Executable Example

SQL Expressions, Complex CASE

Continue

SQL Expressions, Complex CASE