Skip to content

31_WHERE_Conditions.md

Latest commit

 

History

History
677 lines (458 loc) · 19.4 KB

31_WHERE_Conditions.md

File metadata and controls

677 lines (458 loc) · 19.4 KB

WHERE Conditions in ABAP Statements

This cheat sheet focuses on WHERE conditions and explores various syntax options in ABAP statements that include WHERE for data filtering. This is relevant, for example, when retrieving data from a data source using ABAP SQL or when processing internal tables with ABAP statements. For all details and syntax options, refer to the ABAP Keyword Documentation. Several aspects and code snippets in this cheat sheet are also available in other cheat sheets.

Note

  • Most examples in the cheat sheet use internal tables as data sources for ABAP SQL SELECT statements to have self-contained examples. Use SELECT with internal tables as data sources only when SQL functionalities like joins exceed ABAP statements. For more details, refer to the Internal Tables cheat sheet.
  • Some examples also use artifacts from the ABAP cheat sheet repository. To check out these examples, ensure you have imported the ABAP cheat sheet repository into your system.

⬆️ back to top

WHERE Conditions in ABAP SQL Statements

  • Using logical expressions, you can limit the number of rows in the result set or those to be modified in a data source.
  • A row is included in the result set or modified in the data source only if the logical expression is true.
  • Certain restrictions apply to logical expressions. For example, columns of types like string and rawstring cannot be used.
  • Regarding data retrieval, columns in the logical expressions do not need to be part of the result set.
  • You can combine multiple logical expressions using AND or OR.
  • Combining logical expressions in parentheses allows the refinement of conditions. Find more information here.
  • Conditions can be specified in ABAP SQL statements in these contexts:
    • Queries with SELECT: WHERE and HAVING clauses
    • Modifying operations: WHERE clause in UPDATE and DELETE statements

The following table provides an overview of SQL conditions. Note that the code snippets are from the executable example further down.

Syntax Variant Notes Code Snippet

... a = b ...
... a EQ b ...

The content of two operands is equal.

 
SELECT id FROM @itab AS tab 
  WHERE animal = 'bear' 
  INTO TABLE @it.

SELECT id FROM @itab AS tab 
  WHERE animal EQ 'bear' 
  INTO TABLE @it.

... a <> b ...
... a NE b ...

The content of two operands is not equal.

 
SELECT id FROM @itab AS tab 
  WHERE animal <> 'bear' 
  INTO TABLE @it.

SELECT id FROM @itab AS tab 
  WHERE animal NE 'bear' 
  INTO TABLE @it.

... a < b ...
... a LT b ...

The content of the left operand is less than the content of the right operand.

 
SELECT id FROM @itab AS tab 
  WHERE count < 15 
  INTO TABLE @it.

SELECT id FROM @itab AS tab 
  WHERE count LT 15 
  INTO TABLE @it.

... a > b ...
... a GT b ...

The content of the left operand is greater than the content of the right operand.

 
SELECT id FROM @itab AS tab 
  WHERE count > 15 
  INTO TABLE @it.

SELECT id FROM @itab AS tab 
  WHERE count GT 15 
  INTO TABLE @it.

... a <= b ...
... a LE b ...

The content of the left operand is less than or equal to the content of the right operand.

 
SELECT id FROM @itab AS tab 
  WHERE count <= 15 
  INTO TABLE @it.

SELECT id FROM @itab AS tab 
  WHERE count LE 15 
  INTO TABLE @it.

... a >= b ...
... a GE b ...

The content of the left operand is greater than or equal to the content of the right operand.

 
SELECT id FROM @itab AS tab 
  WHERE count >= 15 
  INTO TABLE @it.

SELECT id FROM @itab AS tab 
  WHERE count GE 15 
  INTO TABLE @it.

... AND [NOT] ...
... OR [NOT] ...
... ( ... ) ...

Combining multiple logical expressions into one logical expression using AND or OR (inlcuding negations). To further detail out the desired condition, expressions within parentheses are possible.

 
SELECT id FROM @itab AS tab
  WHERE animal = 'bear' AND count = 5
  INTO TABLE @it.

SELECT id FROM @itab AS tab
  WHERE animal = 'kangaroo' OR count = 4
  INTO TABLE @it.

SELECT id FROM @itab AS tab
  WHERE ( animal = 'bear' AND count = 5 )
  AND ( animal = 'lion' AND count = 20 )
  INTO TABLE @it.

SELECT id FROM @itab AS tab
  WHERE ( animal = 'bear' AND count = 5 )
  OR ( animal = 'lion' AND count = 20 )
  INTO TABLE @it.

SELECT id FROM @itab AS tab
  WHERE count > 10
  OR NOT ( animal = 'kangaroo' AND count = 8 )
  INTO TABLE @it.

... a [=|<>|>|<|...] [ALL|ANY|SOME] ( SELECT ... ) ...

The content of a single operand is compared with the result set of a scalar subquery using a comparison operator. Multiple subqueries can be included with UNION, INTERSECT, or EXCEPT. The additions ALL, ANY, and SOME can only be omitted if the subquery result set contains a single row. Otherwise, an exception occurs with a multi-row result set. One of the additions must be specified in the case of multi-row result set. For ALL, the expression is true if the comparison holds for all rows in the result set. For ANY or SOME, the expression is true if the comparison holds for at least one row. Using = or EQ with ANY or SOME is similar to using IN (SELECT ...).

 
"The following example assumes there is a single-row result set of the subquery.
SELECT id FROM @itab AS tab
  WHERE count = ( SELECT key_field FROM zdemo_abap_tab1 WHERE num1 = 40 )
  INTO TABLE @it.

"ALL addition
SELECT id FROM @itab AS tab
  WHERE count > ALL ( SELECT key_field FROM zdemo_abap_tab1 )
  INTO TABLE @it.    

"ANY addition
SELECT id FROM @itab AS tab
  WHERE count = ANY ( SELECT key_field FROM zdemo_abap_tab1 WHERE num1 <= 40 )
  INTO TABLE @it.

"SOME addition (yields the same result as the previous statement)
SELECT id FROM @itab AS tab
  WHERE count = SOME ( SELECT key_field FROM zdemo_abap_tab1 WHERE num1 <= 40 )
  INTO TABLE @it.

... a [NOT] BETWEEN b AND c ...

The content of the left operand is (not) between the value of the two other operands. The syntax variant is like specifying ... [NOT] ( a >= b AND a <= c ) ....

 
SELECT id FROM @itab AS tab
  WHERE count BETWEEN 1 AND 10
  INTO TABLE @it. 

"Negation with NOT
SELECT id FROM @itab AS tab
  WHERE count NOT BETWEEN 1 AND 10
  INTO TABLE @it.

... a [NOT] LIKE b [ESCAPE c] ...

Checks whether the content of the left operand matches (or does not match) a specified pattern. The pattern can be specified by using wildcard characters. % stands for any character string, including an empty string. _ stands for any character.
Using the ESCAPE addition, you can specify a single-character escape character of length 1 (e.g. #) in front of a wildcard character or the escaped character itself. For example, to search for the pattern 100%, you may use an expression such as the the following: ... LIKE '100#%' ESCAPE '#' ....

 
SELECT animal FROM @itab AS tab
  WHERE animal LIKE '%ee%'
  OR animal LIKE '_e%'
  INTO TABLE @DATA(animals).

"Negation with NOT
SELECT animal FROM @itab AS tab
  WHERE animal NOT LIKE '_e%'
  INTO TABLE @animals.

"ESCAPE addition
"The following example matches any character sequence followed 
"by the % character.
SELECT animal FROM @itab AS tab
  WHERE animal LIKE '%#%' ESCAPE '#'
  INTO TABLE @animals.

... a IS [NOT] INITIAL ...

Checks whether the content of an operand is (not) the initial value of its built-in DDIC type.

 
SELECT id FROM @itab AS tab
  WHERE count IS INITIAL
  INTO TABLE @it. 

"Negation with NOT
SELECT id FROM @itab AS tab
  WHERE count IS NOT INITIAL
  INTO TABLE @it.

... a [NOT] IN (b, c, ...)...

Checks whether the content of the left operand is (not) contained in a set of a parenthesized, comma-separated list of values. You can also specify just one operand in the parentheses.

 
SELECT id FROM @itab AS tab
  WHERE animal IN ( 'elephant', 'gorilla', 'dog', 'snake' )
  INTO TABLE @it.
    
"Negation with NOT
SELECT id FROM @itab AS tab
  WHERE animal NOT IN ( 'chimpanzee', 'dog', 'snake' )
  INTO TABLE @it. 

"Blanks after the first parentheses and before the second are not mandatory.
"This also applies to blanks within the parentheses. However, choose either
"to use the blanks or not.
SELECT id FROM @itab AS tab
  WHERE animal IN ('elephant','gorilla','dog','snake')
  INTO TABLE @it.

... a [NOT] IN ( SELECT ... ) ...

Checks whether the content of the left operand is (not) contained in the result set of a scalar subquery. Note that multiple subqueries can be included using UNION, INTERSECT, EXCEPT.

 
SELECT id FROM @itab AS tab
  WHERE count IN ( SELECT key_field FROM zdemo_abap_tab1 WHERE num1 <= 40 )
  INTO TABLE @it. 

"Negation with NOT
SELECT id FROM @itab AS tab
  WHERE count NOT IN ( SELECT key_field FROM zdemo_abap_tab1 WHERE num1 <= 40 )
  INTO TABLE @it.

... ( a, b, ... ) IN ( ( d, e, ... ) ( f, g, ...) ... ) ...

Checks whether each of the values of multiple operands in a parenthesized, comma-separated list on the left side of IN matches value tuples in the same place specified in parentheses on the right side of IN. Unlike the syntax option ... a [NOT] IN (b, c, ...) ..., this syntax option allows SQL expressions on the right side of IN. Note that a negation with NOT is not supported.

 
SELECT id FROM @itab AS tab
  WHERE ( id, animal ) IN ( ( 1, 'bear' ), ( 3, 'giraffe' ), ( 987, 'flamingo' ), ( 2, 'dog' ) )
  INTO TABLE @it.

... ( a, b, ... ) IN ( SELECT ... ) ...

Checks whether each value of multiple operands in a parenthesized, comma-separated list on the left side of IN matches the content of a subquery result set. The result set must contain the same number of elements as specified in the parentheses on the left side of IN. As above, the position of elements in the result set is relevant. Note that multiple subqueries can be included using UNION, INTERSECT, EXCEPT, and that a negation with NOT is not supported.

 
SELECT id FROM @itab AS tab
  WHERE ( id, count ) IN ( SELECT key_field, num1 FROM zdemo_abap_tab1 )
  INTO TABLE @it.

... a [NOT] IN @ranges_table ...

Checks whether the operand on the left side of IN matches (or does not match) ranges conditions specified in a ranges table. Note that the operators CP and NP are transformed into LIKE conditions (as a consequence, the conditions for CP and NP are case-sensitive).

 
DATA rangestab TYPE RANGE OF i.
"Value range between 1 and 10
rangestab = VALUE #( ( sign = 'I' option = 'BT' low = 1 high = 10 ) ).

SELECT id FROM @itab AS tab
  WHERE count IN @rangestab
  INTO TABLE @it. 

"Value range: Lower than 5 + greater than or equal to 25
rangestab = VALUE #( ( sign   = 'I' option = 'LT' low = 5 )
                     ( sign   = 'I' option = 'GE' low = 25  ) ).

SELECT id FROM @itab AS tab
  WHERE count IN @rangestab
  INTO TABLE @it.

... EXISTS ( SELECT ... ) ...

Checks the result of a subquery. The comparison is true if the result set contains at least one row. Note that data source fields specified in the subquery are not relevant. You may also just use a single literal representing a column. Note that multiple subqueries can be included using UNION, INTERSECT, EXCEPT.

 
SELECT id FROM @itab AS tab
  WHERE EXISTS ( SELECT 'X' FROM zdemo_abap_tab1 WHERE key_field = tab~id )
  INTO TABLE @it.

... a IS [NOT] NULL ...

Checks whether the value of an operand is (not) the null value. Find more information in the executable example (which also includes the INDICATORS NULL STRUCTURE addition to the INTO clause) and in the ABAP Keyword Documentation.

 
SELECT tab2~key_field, tab1~char2
  FROM zdemo_abap_tab2 AS tab2
  LEFT OUTER JOIN zdemo_abap_tab1 AS tab1 ON tab1~char1 = tab2~char1
  WHERE tab1~char1 IS NULL
  INTO TABLE @DATA(joined_tab).

"Negation IS NOT NULL
SELECT tab2~key_field, tab1~char2
  FROM zdemo_abap_tab2 AS tab2
  LEFT OUTER JOIN zdemo_abap_tab1 AS tab1 ON tab1~char1 = tab2~char1
  WHERE tab1~char1 IS NOT NULL
  INTO TABLE @joined_tab.

... (dynamic_where_clause) ...

Dynamic WHERE condition specified as parenthesized data objects. These data objects should contain the syntax of a logical expression. As dynamic_where_clause, a character-like data object or a standard table with character-like line type is expected. The syntax is not case-sensitive. For more information, see the Dynamic Programming cheat sheet, also with respect to potential security risks regarding dynamic specifications.

 
DATA(dynamic_where_clause) = `count > 15`.

SELECT id FROM @itab AS tab
  WHERE (dynamic_where_clause)
  INTO TABLE @it. 

DATA(dyn_where_cl_as_tab) = VALUE string_table( ( `animal = 'kangaroo'` )
                                                ( `OR` )
                                                ( `count = 4` ) ).

SELECT id FROM @itab AS tab
  WHERE (dyn_where_cl_as_tab)
  INTO TABLE @it.

Note

  • Some subqueries in the syntax variants must be scalar subqueries. This means that the subquery returns a single-column result set. The SELECT list of the subquery must only contain a single element.
  • See this topic in the ABAP Keyword Documentation what can be specified as operands on the left and right side.
  • The comparisons are done on the database, so there is no type conversions in ABAP beforehand. Note that platform-dependent conversion behavior may be applied. For SAP HANA Platform-related conversion rules, see this topic.
  • See here a list of comparable types in the ABAP Dictionary.

⬆️ back to top

WHERE Conditions in ABAP Statements for Processing Internal Tables

  • The following ABAP statements and constructor operators, used for processing internal tables, allow WHERE conditions to be specified:
    • LOOP AT
    • READ TABLE
    • DELETE
    • Table filtering using the FILTER constructor operator
    • FOR loops in the context of table comprehensions and reductions (using, e.g., the VALUE - having the exact semantics as LOOP AT; the NEW operator is also possible - and REDUCE operators)
  • The variety of syntax options differs from that available for ABAP SQL statements.
  • The following syntax options are supported for WHERE conditions for these: LOOP AT, READ TABLE (searches for the first line matching the WHERE condition in the internal table), DELETE, and FOR loops.
    • Comparison expressions
    • Predicate expression
      • IS [NOT] INITIAL
      • IS [NOT] BOUND
      • IS [NOT] INSTANCE OF
    • Dynamic WHERE conditions: Any character-like data object or standard table with character-like line type containing logical expressions can be specified within a pair of parentheses.
    • Logical expressions can be combined with AND, OR, EQUIV, also using parenthesized expressions.
  • FILTER constructor operator
    • To use the FILTER operator, certain prerequisites must be met:
      • The source table needs at least one sorted or hash key.
      • If the table lacks such a primary table key, a secondary table key must be available.
    • Only table key columns can be compared with single values in the WHERE condition. The filtering can also be based on a filter table.
    • The operator has restricted syntax options:
      • For source tables with hash keys: Only = is supported for key component comparisons.
      • For source tables with sorted keys: =, EQ, <>, NE, <, LT, >, GT, <=, LE, >=, GE are supported (at least the initial part of the key must be specified).
      • IS [NOT] INITIAL is supported.
      • Multiple comparisons can only be combined using AND.

⬆️ back to top

Executable Example

zcl_demo_abap_where_conditions

Note