Selecting Rows
The WHERE clause restricts the number of rows selected by specifying conditions that must be met.
As well as in the SELECT statement, the WHERE clause is also used in the OPEN CURSOR, UPDATE, and DELETEstatements. The general form of the WHEREclause 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 FROMclause. The result of a condition may be true, false, or unknown. A row is only selected if the condition is true for it. A condition is unknown if one of the columns involved contains a null value.
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 FROMclause, 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.
To find out whether the value of a column lies within a particular interval, use:
SELECT... WHERE s [NOT]BETWEEN f 1 ANDf 2 ...
The condition is true if the value of column s is [not] between the values of the data objects f 1 and f 2 . You cannot use BETWEEN in the ONcondition of the FROM clause.
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 smatches [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 FROMclause.
To find out whether the value of a column is contained in a list of values, use:
SELECT... WHERE s [NOT]IN (f 1 ,......, f n )...
The condition is true if the value of column s is [not] in the list f 1 … f n .
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 rows at all, use:
SELECT... WHERE [NOT]EXISTS subquery...
This condition is true if the result set of the subquery subquery contains at least one [no] row. The subquery does not have to be scalar.
You cannot check a subquery in the ON condition of the FROM clause.
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.
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.
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.
You can join two conditions into one using the AND and ORoperators:
SELECT... WHERE cond 1 ANDcond 2 ...
This condition is true if <cond 1 > and <cond 2 > are true.
SELECT... WHERE cond 1 OR cond 2 ...
This condition is true if one or both of <cond 1 > and <cond 2 > are true.
NOT takes priority over AND, and AND takes priority over OR. However, you can also control the processing sequence using parentheses.
To specify a condition dynamically, use:
SELECT... WHERE (itab)...
where itab is an internal table with row type c and maximum length 72 characters. All of the conditions listed above except for selection tables, can be written to the rows 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 join a static and a dynamic condition using OR.
You may only use dynamic conditions in the WHEREclause of the SELECT statement.
The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the rows and columns of an internal table: This variant is as follows:
SELECT... FOR ALLENTRIES 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 rows of the internal table. The comparison is then performed for each row of the internal table. For each row, the system selects the rows from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each row of the internal table. Duplicate rows are automatically eliminated from the results set. If itabis empty, the addition FOR ALLENTRIES is ignored, and all entries are read.
The internal table itab must have a structured row type, and each field that occurs in the condition condmust be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN andINin comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.
You can use the option FOR ALLENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.
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 AND45.
This condition is true if the column NUM contains numbers between 15 and 45.
... WHERE num NOT BETWEEN 1 AND99.
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 CITYcontains 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', 'NEWYORK', 'LONDON').
This condition is true if the column CITYcontains one of the values BERLIN, NEWYORK, or LONDON.
... WHERE city NOT IN ('FRANKFURT', 'ROM').
This condition is true if the column CITYdoes not contain the values FRANKFURT or ROME.
... WHERE ( NUMbER = '0001' OR number = '0002') AND NOT ( country = 'F' OR country = 'USA').
This condition is true if the column NUMBER contains the value 0001 or 0002 and the column COUNTRY contains neither F nor USA.
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.
DATAwa 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 city2on the selection screen, the list display is as follows:
The first three lines show the contents of the internal table itab. Exactly the corresponding table rows are selected.
Table-like condition
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 = 'NEWYORK'.line-cityto = 'SANFRANCISCO'.APPEND line TO itab.
SELECT carrid connid cityfrom cityto INTO CORRESPONDING FIELDS OF line FROM spfli FOR ALLENTRIES IN itab WHERE cityfrom = itab-cityfrom ANDcityto = itab-cityto.
WRITE: / line-carrid, line-connid, line-cityfrom, line-cityto.
ENDSELECT.
The output looks something like this:
This example selects all rows in which the following conditions are met:
- The CITYFROM column contains FRANKFURT and the CITYTO column contains BERLIN.
- The CITYFROM column contains NEW YORKand the CITYTO column contains SANFRANCISCO.Tip
Table-like condition
REPORT demo_select_for_all_entries_1.
DATA: tab_spfli TYPE TABLE OF spfli, tab_sflight TYPE SORTED TABLE OF sflight WITH UNIQUE KEYtable_line, wa LIKE LINEOF tab_sflight.
SELECT carrid connidINTO CORRESPONDING FIELDS OF TABLE tab_spfliFROM spfliWHERE cityfrom = 'NEWYORK'.
SELECT carrid connid fldate INTO CORRESPONDING FIELDS OF TABLE tab_sflight FROM sflight FOR ALLENTRIES IN tab_spfli WHERE carrid = tab_spfli-carrid AND connid = tab_spfli-connid.
LOOP AT tab_sflight INTO wa.
AT NEWconnid. WRITE: / wa-carrid, wa-connid. ENDAT.
WRITE: / wa-fldate.
ENDLOOP.
The output looks something like this:
This example selects flight data from SFLIGHT for all connections for which the column CITYFROM in table SPFLI has the value NEWYORK. You could also use a join in the FROM clause to select the same data in a single SELECT statement.