WHERE Clause

WHERE clause

Variants:

1. ... WHERE f op g
2. ... WHERE f [NOT] BETWEEN g1 AND g2
3. ... WHERE f [NOT] LIKE g
4. ... WHERE f [NOT] IN (g1, ..., gn)
5. ... WHERE f [NOT] IN itab
6. ... WHERE f IS [NOT] NULL
7. ... WHERE NOT cond
8. ... WHERE cond1 AND cond2
9. ... WHERE cond1 OR cond2
10. ... WHERE (itab)
11. ... WHERE cond AND (itab)
12. ... FOR ALL ENTRIES IN itab WHERE cond

Effect

If a WHERE clause is specified with the commands SELECT, OPEN CURSOR, UPDATE and DELETE , only the lines of the database table (or view) which satisfy the specified condition(s) are selected.

With Open SQL key words, automatic client handling is normally active. This ensures that only data from the current client is processed when you are working with client-specific tables. Therefore, specifying a client in the WHERE clause does not make sense and is rejected as an error by the syntax check.

If you use the addition ... CLIENT SPECIFIED in the FROM clause to switch off automatic client handling, the client field is treated like a normal table field and you can formulate conditions for it in the WHERE clause.

If, when using transparent tables, there are frequent accesses without a complete primary key or the data is sorted in an order other than by the primary key, you should consider whether it is worth creating an index.

Effect

The condition is true if the comparison f op g is true. The condition is false if the comparison f op g is false. Here, f is the name of a database field (without a prefix) and g is the name of any field or literal. You can use any of the following relational operators:

, = EQual
NE, <>, >< Not Equal
LT, < Less Than
LE, <= Less than or Equal
GT, > Greater Than
GE, >= Greater than or Equal

... WHERE CARRID = 'LH'

Select passenger planes with fewer than 200 seats:

... WHERE SEATSMAX LT 200

If the database field f contains the NULL value, the result of evaluating the condition is neither "true" nor "false", but "unknown".

Example

If a line contains the NULL value for the field TELEPHONE, you cannot use any of the following conditions to select this line:

... WHERE TELEPHONE = ' '

... WHERE TELEPHONE <> ' '

... WHERE NOT TELEPHONE = ' '

Effect

The condition is true, if the contents of the table field f (do not) lie between g1 and g2. Otherwise, the condition is false.

Examples

Select all passenger planes with between 200 and 250 seats:

... WHERE SEATSMAX BETWEEN 200 AND 250

Note

If the database field f contains the NULL value, the result of evaluating the condition is neither "true" nor "false", but "unknown".


... ESCAPE h

Effect

The condition is true, if the contents of the table field f (do not) correspond to the contents of the field g . Within the search pattern, two characters have a particular meaning:

If the statement does not apply, the condition is false.

Examples

Select all customers whose names begin with 'M':

... WHERE NAME LIKE 'M%'

Select all texts which contain the word 'customer':

... WHERE TEXT LIKE '%customer%'

Select all customers whose names do not contain 'n' as the second letter:

... WHERE NAME NOT LIKE '_n%'

You can apply LIKE only to alphanumeric database fields, i.e. the table field f must be one of the Dictionary types ACCP, CHAR, CLNT, CUKY, LCHR, NUMC, UNIT, VARC, TIMS or DATS. The comparison field g must always be type C.

Effect

The field h contains an escape symbol. Within the pattern g, this makes a special character following the escape symbol lose its special meaning.

Example

Select all function modules whose names begin with 'EDIT_':

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

An escape symbol can only precede one of the special characters '%' and '_' or itself.

Effect

The condition is true, if the contents of the table field f are (not) the same as the contents of one of the fields or literals g1, ..., gn. Otherwise, the condition is false.

Examples

Select the flight connections of American Airlines, Lufthansa and Singapore Airlines:

... WHERE CARRID IN ('AA', 'LH', 'SQ')

Select all flight connections apart from those of Lufthansa and Lauda Air:

... WHERE CARRID NOT IN ('LH', 'NG')

There must be no blanks between the opening parenthesis which introduces the field list and the name g1 of the first field in the field list.

Effect

The condition is true, if the contents of the field satisfy the conditions in the internal table itab.

The internal table itab must have the structure of a RANGES table for f. You can define it with RANGES itab FOR f, SELECT-OPTIONS itab FOR f or DATA. If itab is defined with SELECT-OPTIONS, it is automatically filled with the user's predefined values. Otherwise, you must specify it explicitly in the program. This is a method of specifying parts of the WHERE condition at runtime.

Each line of itab contains an elementary condition where the columns have the following meaning:

SIGN Specifies whether the condition is inclusive or exclusive. Possible values are:

I Inclusive
E Exclusive

OPTION Contains the operator for the elementary condition. Possible values are:

EQ, NE EQual, Not Equal
BT, NB BeTween, Not Between
CP, NP Contains Pattern,
does Not contain Pattern
LT, LE Less Than, Less than or Equal
GT, GE Greater Than, Greater than or Equal

LOW With EQ, NE, LT, LE, GT and GE, this field contains the compare value. With BT and NB , it contains the lower limit of a range. With CP and NP, it can extend beyond LOW and HIGH.

HIGH With BT and NB, this field contains the upper limit of a range. With CP and NP, it contains the end of the specification begun in LOW.

The elementary conditions in itab are combined together to form a complex condition in the following manner:

but not the customer numbers

TABLES: SCUSTOM. 
 
SELECT-OPTIONS: R FOR SCUSTOM-ID. 
* RANGES:	 R FOR SCUSTOM-ID. 
 
* Let R be filled as follows (the order of lines is 
* of no significance): 
* 
* SIGN  OPTION  LOW	 HIGH 
* I	 EQ	01104711 
* I	 BT	10000000  19999999 
* I	 GE	90000000 
* E	 EQ	10000911 
* E	 BT	10000810  10000815 
* E	 CP	++++5* 
* 
* This generates the condition 
* 
* ( 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 '____5%' 
* 
SELECT * FROM SCUSTOM WHERE ID IN R. 
  ... 
ENDSELECT.

Since a condition of the form f IN itab triggers a complex condition at runtime, but the size of the SQL statement is restricted by the underlying database system (e.g. no more than 8 K), the internal table itab must not contain too many lines.

Effect

The condition is true if the contents of the table field f (do not) contain the NULL value.

Example

Select all customers with customer numbers for which no telephone number is specified:

... WHERE TELEPHONE IS NULL

The SAP buffer does not support this variant. Therefore, the effect of each SELECT command on a buffered table or on a view of fields from buffered tables that contains ... WHERE f IS [NOT] NULL is as if the addition BYPASSING BUFFER was specified in the FROM clause.

Effect

NOT cond is true if cond is false. The condition is false of cond is true. This produces the following truth table:

NOT
true false
false true
unknown unknown

cond can be any condition according to the WHERE variants 1 - 9. NOT takes priority over AND and OR . You can also determine the evaluation sequence by using parentheses.

Note

Parentheses which determine the evaluation sequence must be preceded and followed by a blank.

Example

Select the customers with customer numbers who do not live in postal code area 68:

... WHERE NOT POSTCODE LIKE '68%'

Effect

cond1 AND cond2 is true if cond1 and cond2 are true. The condition is false if cond1 or cond2 is false. This produces the following truth table:

AND true false unknown
true true false unknown
false false false false
unknown unknown false unknown

cond1 and cond2 can be any conditions according to the WHERE variants 1 - 9. AND takes priority over OR, but NOT takes priority over AND. You can also determine the evaluation sequence by using prenetheses.

Note

Parentheses which determine the evaluation sequence must be preceded and followed by a blank.

Example

Select the customers with customer numbers which are less than '01000000' and do not live in the postal code area 68.

... WHERE ID < '01000000'
AND NOT
POSTCODE LIKE '68%'

Effect

cond1 OR cond2 is true if cond1 or cond2 is true. The condition is false if cond1 and cond2 are false. This produces the following truth table:

OR true false unknown
true true true true
false true false unknown
unknown true unknown unknown

cond1 and cond2 can be any conditions according to the WHERE variants 1 - 9. Both NOT and AND take priority over OR . You can also determine the evaluation sequence by using parentheses.

Note

Parentheses which determine the evaluation sequence must be preceded and followed by a blank.

Example

Select the customers with customer numbers which are less than '01000000' or greater than '02000000':

... WHERE ID < '01000000' OR
ID > '02000000'.

Select the customers with customer numbers which are less than '01000000' or greater than '02000000' and do not live in the postal code areas 68 or 69

... WHERE ( ID < '01000000' OR ID > '02000000' )
AND NOT
( POSTCODE LIKE '68%' OR POSTCODE LIKE '69%' )

Effect

The condition is true if the contents of the table fields satisfy the condition stored in the internal table itab. itab is filled at runtime, i.e. the condition for the fields is specified dynamically.

This variant is exclusively for use with SELECT. The internal table itab can only have one field which must be of type C and not be greater than 72 characters. itab must be specified in parentheses with no blanks between the parentheses and the table name. The condition specified in the internal table itab must have the same form as a condition in the ABAP/4 source code. The following restrictions apply:

- You can only use literals as values, not variables.

- The operator IN cannot be used in the form f1 IN itab1.

Since the syntax check may not be performed until runtime, a WHERE condition needs more execution time than a corresponding specification in the program code.

Example

Display flight connections after entry of airline carrier and flight number:

TABLES:	 SPFLI. 
PARAMETERS: CARR_ID LIKE SPFLI-CARRID, 
			CONN_ID LIKE SPFLI-CONNID. 
DATA:	 WTAB(72) OCCURS 100 WITH HEADER LINE, 
			AND(3). 
 
REFRESH WTAB. 
IF NOT CARR_ID IS INITIAL. 
  CONCATENATE 'CARRID = ''' CARR_ID '''' INTO WTAB. 
  APPEND WTAB. 
  AND = 'AND'. 
ENDIF. 
IF NOT CONN_ID IS INITIAL. 
  CONCATENATE AND ' CONNID = ''' CONN_ID '''' INTO WTAB. 
  APPEND WTAB. 
ENDIF. 
 
SELECT * FROM SPFLI WHERE (WTAB). 
  WRITE: / SPFLI-CARRID, SPFLI-CONNID, SPFLI-CITYFROM, 
		 SPFLI-CITYTO, SPFLI-DEPTIME. 
ENDSELECT.

Effect

Like variant 10. For the condition to be true, the table fields must also satisfy the condition cond.

Note

When specifying a condition cond in the program code together with a condition in an internal table itab, the table name must appear in parentheses after the condition cond and be linked with AND. There must be no blanks between the name of the internal table and the parentheses.

Effect

Selects only those lines of the database table which satisfy the WHERE condition cond where each occurring replacement symbol itab-f is replaced by the value of the component f in the internal table itab for at least one line. Clearly, a SELECT command with ... FOR ALL ENTRIES IN itab WHERE cond forms the union of solution sets for all SELECT commands which result when, for each line of the internal table itab, each symbol itab-f addressed in the WHERE condition is replaced by the relevant value of the component f in this table line. Duplicate lines are eliminated from the result set. If the internal table itab contains no entries, the processing continues as if there were no WHERE condition cond.

TABLES SFLIGHT. 
DATA:  BEGIN OF FTAB OCCURS 10, 
		 CARRID LIKE SFLIGHT-CARRID, 
		 CONNID LIKE SFLIGHT-CONNID, 
	 END OF FTAB, 
	 RATIO TYPE F. 
 
* Let FTAB be filled as follows: 
* 
* CARRID  CONNID 
* -------------- 
* LH	2415 
* SQ	0026 
* LH	0400 
 
SELECT * FROM SFLIGHT FOR ALL ENTRIES IN FTAB 
					WHERE CARRID = FTAB-CARRID AND 
							CONNID = FTAB-CONNID AND 
							FLDATE = '19950228'. 
  RATIO = SFLIGHT-SEATSOCC / SFLIGHT-SEATSMAX. 
  WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, RATIO. 
ENDSELECT. 

... FOR ALL ENTRIES IN itab WHERE cond can only be used with a SELECT command.