Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Conditions sql_cond →  sql_cond - rel_exp for Statements 

sql_cond - IN range_tab

Other versions: 7.31 | 7.40 | 7.54

Syntax


... operand [NOT] IN @range_tab ...

Effect

This variant of the operator IN checks whether the operands on the left side match a ranges condition in a ranges table. The relational expression is true if the value of the operand operand is (not) in the results set described in the rows of the ranges table range_tab specified as a host variable.

  • The following applies to operand:
  • Any internal table with a row type that corresponds to that of a ranges table can be specified for the ranges table range_tab. This includes, in particular, selection tables.

The ranges table is evaluated in the same way as in comparison expressions, with the difference that any comparisons using the operators CP and NP are transformed into LIKE conditions for which the ABAP SQL escape character "#" is defined. The pattern after CP or NP is transformed to a pattern for LIKE as follows:

  • If the ABAP SQL wildcard characters "%" are contained in the pattern, the "#" escape character is inserted before these characters.
  • Any wildcard characters "*" and "+" that are not prefixed with the escape character "#" are transformed to the ABAP SQL wildcard characters "%" and "_".
  • Any "#" escape characters that do not prefix themselves or the ABAP SQL wildcard characters "%" and "_" are removed.

If the ranges table is initial, the expression IN range_tab is always true.

The content of the columns LOW and HIGH in the ranges table must match the data type of the operand in accordance with the rules for lossless assignments. This is checked by the strict modes of the syntax check from Release 7.40, SP08 and can raise an exception.


Notes

  • LIKE conditions are case-sensitive, which is not the case in ABAP comparison expressions.

  • If no conditions are specified apart from IN range_tab, all rows of the data source are selected if the ranges table is initial.

  • The conditions specified in the ranges table are passed by the database interface to the database as SQL statement input values. The maximum number of input values of this type depends on the database system and is between 2000 and 10000. If this maximum is exceeded, an exception of the class CX_SY_OPEN_SQL_DB is raised.

  • If the ranges table contains invalid values, an exception that cannot be handled is raised.

  • No host expression can be specified on the right side whose result represents a ranges table.


Example

A ranges table is filled as follows (the order of the rows is not important):

SIGN  OPTION  LOW              HIGH
---------------------------------------
I     EQ      01104711
I     BT      10000000         19999999
I     GE      90000000
E     EQ      10000911
E     BT      10000810         10000815
E     CP      1%2##3#+4++5*

When used in sql_cond, this generates the following join of relational expressions:

... ( ID = '01104711'                      OR
      ID BETWEEN '10000000' AND '19999999' OR
      ID >= '90000000' )                     AND
    ID <> '10000911'                         AND
    ID NOT BETWEEN '10000810' AND '10000815' AND
    ID NOT LIKE '1#%2##3+4__5%' ESCAPE '#'   ...


Example

Uses a selection table defined for a selection screen with SELECT-OPTIONS in a WHERE clause.

DATA carrid TYPE spfli-carrid. 
SELECT-OPTIONS airlines FOR carrid. 

SELECT carrid, connid, cityfrom, cityto 
       FROM spfli 
       WHERE carrid IN @airlines 
       INTO TABLE @DATA(flights). 

cl_demo_output=>display( flights ).