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_simple_case
Other versions:
7.31 | 7.40 | 7.54
Syntax
... CASE sql_exp
WHEN sql_exp1 THEN result1
[WHEN sql_exp2 THEN result2]
...
[ELSE resultn]
END ...
Effect
Simple case distinction (simple case) in ABAP SQL. This
SQL expression compares the values of the operand sql_exp with the operands
sql_exp1, sql_exp2, and so on, and produces the
operand result as the result after the first THEN
for which the comparison is true. If no matches are found, the result specified after ELSE is selected. If ELSE is not specified, the result is the
zero value.
The operands sql_exp, sql_exp1, sql_exp2,
and so on, and the results result1, result2, and so on can be any
SQL expressions. They can have any dictionary types except ACCP, DF16_SCL (obsolete), DF34_SCL (obsolete), LCHR, LRAW, PREC, RAWSTRING, STRING, and GEOM_EWKB.
The data type of the operand sql_exp must be
comparable with the data types of the
operands sql_exp1, sql_exp2, and so on. 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 results must also be compatible to
produce a common result type: 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. The result has the dictionary type of the entry with the greatest value range.
If an operand of a comparison has the null value, the result of this comparison is unknown.
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.
- If an operand of a simple
CASEexpression is an SQL expression, the syntax check is performed in a strict mode that handles the statement more strictly than the regular syntax check.
Example
Converts the distances specified in miles to kilometers. The conversion factor is specified as the host
variable, whose value has already been calculated using an SQL expression in a SELECT
statement. This task could also be performed more generally in a subquery of a WITH statement.
SELECT SINGLE
FROM t006
FIELDS division( zaehl,nennr * 1000,2 ) AS factor
WHERE msehi = 'MI'
INTO @DATA(factor).
SELECT FROM spfli
FIELDS carrid,
connid,
cityfrom,
cityto,
CASE distid
WHEN 'MI' THEN distance * @factor
ELSE distance
END AS distance,
'KM' AS distid
WHERE distid = 'KM' OR distid = 'MI'
ORDER BY carrid, connid
INTO TABLE @DATA(result).
cl_demo_output=>display( result ).