Selecting Lines (SAP Library - ABAP Programming (BC-ABA))

Selecting Lines

The WHERE clause restricts the number of lines selected by specifying conditions that must be met.

This graphic is explained in the accompanying text

As well as in the SELECT statement, the WHERE clause is also used in the OPEN CURSOR, UPDATE, and DELETE statements. The general form of the WHERE clause is:

SELECT... WHERE cond...

The cond conditions in the WHERE clause can be comparisons or a series of other special expressions. You can combine a series of conditions into a single condition in the WHERE clause. Conditions may also be programmed dynamically.

The conditions cond in the WHERE clause are often like logical expressions, but not identical, since the syntax and semantics follow that of Standard SQL. In the conditions in the WHERE clause, you name columns using a field name as in the SELECT clause. In the following descriptions, s always represents a column of one of the database tables named in the FROM clause. The result of a condition may be true, false, or unknown. A line is only selected if the condition is true for it. A condition is unknown if one of the columns involved contains a null value.

Comparisons for All Types

To compare the value of a column of any data type with another value, use the following:

SELECT... WHERE s operator f...

f can be another column in a database table from the FROM clause, a data object, or a scalar subquery.

You can use the following expressions for the relational operator:

operator	Meaning
EQ	Equals
=	Equals
NE	Does not equal
<>	Does not equal
><	Does not equal
LT	less than
<	less than
LE	Less than or equal to
<=	Less than or equal to
GT	greater than
>	greater than
GE	Greater than or equal to
>=	Greater than or equal to

The values of the operands are converted if necessary. The conversion may be dependent on the platform and codepage.

Values in Intervals

To find out whether the value of a column lies within a particular interval, use:

SELECT... WHERE s [NOT] BETWEEN f₁ AND f₂...

The condition is true if the value of column s is [not] between the values of the data objects f₁ and f₂. You cannot use BETWEEN in the ONcondition of the FROM clause.

Comparing Strings

To find out whether the value of a column matches a pattern, use:

SELECT... WHERE s [NOT] LIKE f [ESCAPE h]...

The condition is true if the value of the column s matches [does not match] the pattern in the data object f. You can only use this test for text fields. The data type of the column must be alphanumeric. f must have data type c.

You can use the following wildcard characters in f:

· % for a sequence of any characters (including spaces).

· _ for a single character.

For example, ABC_EFG% matches the strings ABCxEFGxyz and ABCxEFG, but not ABCEFGxyz. If you want to use the two wildcard characters explicitly in the comparison, use the ESCAPE option. ESCAPE h specifies an escape symbol h. If preceded by h, the wildcards and the escape symbol itself lose their usual function within the pattern f. The use of _ and % corresponds to Standard SQL usage. Logical expressions elsewhere in ABAP use other wildcard characters (+ and *).

You cannot use LIKE in the ON condition of the FROM clause.

Checking Lists of Values

To find out whether the value of a column is contained in a list of values, use:

SELECT... WHERE s [NOT] IN (f₁,......, f_n)...

The condition is true if the value of column s is [not] in the list f₁ … f_n .

Checking Subqueries

To find out whether the value of a column is contained in a scalar subquery, use:

SELECT... WHERE s [NOT] IN subquery...

The condition is true if the value of <s> is [not] contained in the results set of the scalar subquery subquery.

To find out whether the selection of a subquery contains lines at all, use:

SELECT... WHERE [NOT] EXISTS subquery...

This condition is true if the result set of the subquery subquerycontains at least one [no] line. The subquery does not have to be scalar.

You cannot check a subquery in the ONcondition of the FROM clause.

Checking Selection Tables

To find out whether the value of a column satisfies the conditions in a selection table, use:

SELECT... WHERE s [NOT] IN seltab...

The condition is true if the value of s [does not] satisfy the conditions stored in seltab. seltab can be either a real selection table or a RANGES table. You cannot check a selection table in the ON condition of the FROM clause.

Checking for Null Values

To find out whether the value of a column is null, use:

SELECT... WHERE s IS [NOT] NULL...

The condition is true if the value of s is [not] null.

Negating Conditions

To negate the result of a condition, use:

SELECT... WHERE NOT cond...

The condition is true if cond is false, and false if cond is true. The result of an unknown condition remains unknown when negated.

Linking Conditions

You can combine two conditions into one using the AND and ORoperators:

SELECT... WHERE cond₁ AND cond₂...

This condition is true if <cond₁> and <cond₂> are true.

SELECT... WHERE cond₁ OR cond₂...

This condition is true if one or both of <cond₁> and <cond₂> are true.

NOT takes priority over AND, and AND takes priority over OR. However, you can also control the processing sequence using parentheses.

Dynamic Conditions

To specify a condition dynamically, use:

SELECT... WHERE (itab)...

where itab is an internal table with line type c and maximum length 72 characters. All of the conditions listed above except for selection tables, can be written into the lines of itab. However, you may only use literals, and not the names of data objects. The internal table itab can also be left empty.

If you only want to specify a part of the condition dynamically, use:

SELECT... WHERE cond AND (itab)...

You cannot link a static and a dynamic condition using OR.

You may only use dynamic conditions in the WHERE clause of the SELECT statement.

Tabular Conditions

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table: This variant is as follows:

SELECT... FOR ALL ENTRIES IN itab WHERE cond...

cond may be formulated as described above. If you specify a field of the internal table itab as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If itab is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table itab must have a structured line type, and each field that occurs in the condition cond must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN andIN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIESto replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

Example

Conditions in the WHERE clause:

... WHERE carrid = 'UA'.

This condition is true if the column CARRID has the contents UA.

... WHERE num GE 15.

This condition is true if the column NUM contains numbers greater than or equal to 15.

... WHERE cityfrom NE 'FRANKFURT'.

This condition is true if the column CITYFROM does not contain the string FRANKFURT.

... WHERE num BETWEEN 15 AND 45.

This condition is true if the column NUM contains numbers between 15 and 45.

... WHERE num NOT BETWEEN 1 AND 99.

This condition is true if the column NUM contains numbers not between 1 and 99.

... WHERE name NOT BETWEEN 'A' AND 'H'.

This condition is true if the column NAME is one character long and its contents are not between A and H.

... WHERE city LIKE '%town%'.

This condition is true if the column CITY contains a string containing the pattern ‘town’.

... WHERE name NOT LIKE '_n%'.

This condition is true if the column NAME contains a value whose second character is not ‘n’.

... WHERE funcname LIKE 'EDIT#_%' ESCAPE '#'.

This condition is true if the contents of the column FUNCNAME begin with EDIT_.

... WHERE city IN ('BERLIN', 'NEW YORK', 'LONDON').

This condition is true if the column CITY contains one of the values BERLIN, NEW YORK, or LONDON.

... WHERE city NOT IN ('FRANKFURT', 'ROM').

This condition is true if the column CITY does not contain the values FRANKFURT or ROME.

... .......WHERE ( NUMBER = '0001' OR NUMBER = '0002' ) AND

This condition is true if the column NUMBER contains the value 0001 or 0002 and the column COUNTRY contains neither F nor USA.

Example

Dynamic conditions

REPORT demo_select_dynamic_conditions.

DATA: cond(72) TYPE c,
itab LIKE TABLE OF cond.

PARAMETERS: city1(10) TYPE c, city2(10) TYPE c.

DATA wa TYPE spfli-cityfrom.

CONCATENATE 'CITYFROM = ''' city1 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' city2 '''' INTO cond.
APPEND cond TO itab.
CONCATENATE 'OR CITYFROM = ''' 'BERLIN' '''' INTO cond.
APPEND cond TO itab.

LOOP AT itab INTO cond.
WRITE cond.
ENDLOOP.

SKIP.

SELECT  cityfrom
  INTO  wa
  FROM  spfli
  WHERE (itab).

WRITE / wa.

ENDSELECT.

If the user enters FRANKFURT and BERLIN for the parameters city1 and city2 on the selection screen, the list display is as follows:

This graphic is explained in the accompanying text

The first three lines show the contents of the internal table itab. Exactly the corresponding table lines are selected.

Example

Tabular conditions

REPORT demo_select_for_all_entries_1.

DATA: BEGIN OF line,
        carrid   TYPE spfli-carrid,
        connid   TYPE spfli-connid,
        cityfrom TYPE spfli-cityfrom,
        cityto   TYPE spfli-cityto,
      END OF line,
      itab LIKE TABLE OF line.

line-cityfrom = 'FRANKFURT'.
line-cityto = 'BERLIN'.
APPEND line TO itab.

line-cityfrom = 'NEW YORK'.
line-cityto = 'SAN FRANCISCO'.
APPEND line TO itab.

SELECT  carrid connid cityfrom cityto
  INTO  CORRESPONDING FIELDS OF line
  FROM  spfli
  FOR ALL ENTRIES IN itab
  WHERE cityfrom = itab-cityfrom AND cityto = itab-cityto.

WRITE: / line-carrid, line-connid, line-cityfrom, line-cityto.

ENDSELECT.

The output looks something like this:

This graphic is explained in the accompanying text

This example selects all lines in which the following conditions are fulfilled:

§ The CITYFROM column contains FRANKFURT and the CITYTO column contains BERLIN.

§ The CITYFROM column contains NEW YORK and the CITYTO column contains SAN FRANCISCO.

Example

Tabular conditions

REPORT demo_select_for_all_entries_2.

DATA: tab_spfli   TYPE TABLE OF spfli,
      tab_sflight TYPE SORTED TABLE OF sflight
                       WITH UNIQUE KEY table_line,
      wa LIKE LINE OF tab_sflight.

SELECT carrid connid
INTO   CORRESPONDING FIELDS OF TABLE tab_spfli
FROM   spfli
WHERE  cityfrom  = 'NEW YORK'.

SELECT  carrid connid fldate
  INTO  CORRESPONDING FIELDS OF TABLE tab_sflight
  FROM  sflight
  FOR ALL ENTRIES IN tab_spfli
  WHERE carrid = tab_spfli-carrid AND
        connid = tab_spfli-connid.

LOOP AT tab_sflight INTO wa.

  AT NEW connid.
    WRITE: / wa-carrid, wa-connid.
  ENDAT.

WRITE: / wa-fldate.

ENDLOOP.

The output looks something like this:

This graphic is explained in the accompanying text

This example selects flight data from SFLIGHT for all connections for which the column CITYFROM in table SPFLI has the value NEW YORK. You could also use a join in the FROMclause to select the same data in a single SELECTstatement.