Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT →  SELECT - WHERE →  WHERE - sql_cond 

sql_cond - IN seltab

Other versions: 7.31 | 7.40 | 7.54

Syntax


... col [NOT] IN seltab ...

Effect

This expression is true if the value of the column col is (not) in the results set described in the rows of the selection table seltab. The selection table seltab (whose name should be prefixed with the escape character @ like every host variable) can be any internal table whose row type matches a selection table. This includes, in particular, ranges tables.

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

  • If the Open 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 Open SQL wildcard characters "%" and "_".
  • Any "#" escape characters that do not prefix themselves or the Open SQL wildcard characters "%" and "_" are removed.

If the selection table is initial, the expression IN seltab is always true.

The content of the columns LOW and HIGH in the selection table should match the data type of the column 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 seltab, all rows of the database table are selected if the selection table is initial.

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

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

  • Specifying the selection table without the escape character @ is obsolete. The escape character @ must be specified in the strict modes of the syntax check from Release 7.40, SP05.


Example

A selection 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*

The following WHERE condition is generated from this:

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

Reads flights with a primary key that corresponds to the user entries on the selection screen.

DATA spfli_wa TYPE spfli. 

SELECT-OPTIONS: s_carrid FOR spfli_wa-carrid NO INTERVALS 
                                           NO-EXTENSION, 
                s_connid FOR spfli_wa-connid NO INTERVALS 
                                            NO-EXTENSION. 

SELECT SINGLE * 
       FROM spfli 
       WHERE carrid IN @s_carrid AND 
             connid IN @s_connid 
       INTO @spfli_wa.

Continue

Selection Tables in the WHERE Clause - Example