Logical Condition in Database Access Statements

Logical Condition

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

Effect

Specified in the

to check whether data from various

database tables or views meets certain criteria.

Effect

The condition is fulfilled for a table line if the statement "f op g" is true for the values of f and g. f must always be a field descriptor and g either a field descriptor , an ABAP field or a scalar subquery . You can use any of the following relational operators:

EQ, = (EQual)
NE, <>, >< (Not Equal)
LT, < (Less Than)
LE, <=, =< (Less Equal)
GT, > (Greater Than)
GE, >=, => (Greater Equal)

TABLES SPFLI. 
 
SELECT * FROM SPFLI 
	 WHERE CARRID = 'LH'. 
  WRITE: / SPFLI-CITYFROM, SPFLI-CITYTO. 
ENDSELECT.

To select all flights that are not fully booked:

TABLES SFLIGHT. 
 
SELECT * FROM SFLIGHT 
	 WHERE SFLIGHT~SEATSOCC LT SFLIGHT~SEATSMAX. 
  WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, SFLIGHT-FLDATE. 
ENDSELECT.

To select the flights with the greatest number of passengers:

TABLES SFLIGHT. 
 
SELECT * FROM SFLIGHT 
	 WHERE SEATSOCC = ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ). 
  WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, SFLIGHT-FLDATE. 
ENDSELECT.

If the statement "f op g" is not true, it is not automatically false: It is only false, if neither f nor g has the value NULL. If f or g is ZERO, the check returns the value unknown for the statment "f op g ". See also variant 7.

Example

None of the following commands contains a line in which the field TELEPHONE contains the value NULL:

TABLES SCUSTOM. 
 
SELECT * FROM SCUSTOM WHERE TELEPHONE = ' '. 
ENDSELECT. 
SELECT * FROM SCUSTOM WHERE TELEPHONE <> ' '. 
ENDSELECT. 
SELECT * FROM SCUSTOM WHERE NOT TELEPHONE = ' '. 
ENDSELECT.

If f and g are field identifiers, they must have the same type and length. Otherwise, the results depend on the database system in use.

Effect

The condition is met for a table entry if the statement "f is (not) between g1 and g2" is true for the values of f, g1 and g2. f must always be a field descriptor and g1 and g2 ABAP fields. If f has the value NULL, the check for the statement returns unknown.

Example

Example to select all passenger aircraft with 200 - 250 seats.

TABLES SAPLANE. 
DATA:  LOW  TYPE I VALUE 200, 
	 HIGH TYPE I VALUE 250. 
 
SELECT * FROM SAPLANE 
	 WHERE SEATSMAX BETWEEN LOW AND HIGH. 
  WRITE: / SAPLANE-PLANETYPE. 
ENDSELECT.

Note

You cannot use this variant in the ON addition of the FROM clause.


Addition:
... ESCAPE h

Effect

The condition is met for a table entry if the statement "f (does not) equal the pattern in g" is true for the values of f and g. f must always be a field descriptor, and g an ABAP field. If f has the value NULL, then the result of the check for the statement is unknown. Within a pattern, there are two special characters:

Examples

Example to select all customers whose name begins with 'M':

TABLES SCUSTOM. 
 
SELECT ID NAME FROM SCUSTOM 
	 INTO CORRESPONDING FIELDS OF SCUSTOM 
	 WHERE NAME LIKE 'M%'. 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME. 
ENDSELECT.

Example to select all customers whose name contains 'huber':

TABLES SCUSTOM. 
 
SELECT ID NAME FROM SCUSTOM 
	 INTO CORRESPONDING FIELDS OF SCUSTOM 
	 WHERE NAME LIKE '%huber%'. 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME. 
ENDSELECT.

Example to select all customers whose name does not contain 'n' as the second character:

TABLES SCUSTOM. 
 
SELECT ID NAME FROM SCUSTOM 
	 INTO CORRESPONDING FIELDS OF SCUSTOM 
	 WHERE NAME NOT LIKE '_n%'. 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME. 
ENDSELECT.

LIKE can only be used for alphanumeric database fields. In other words, table field f must have Dictionary type ACCP , CHAR, CLNT, CUKY, LCHR, NUMC, UNIT, VARC, TIMS or DATS . The comparison field g must always have type C.

Effect

The field h contains an escape symbol. A special character within the pattern g is not regarded as a special character if it is preceded by the escape character.

Example

Example to select all customers whose name begins with '100%':

TABLES SCUSTOM. 
 
SELECT ID NAME FROM SCUSTOM 
	 INTO CORRESPONDING FIELDS OF SCUSTOM 
	 WHERE NAME NOT LIKE '100#%' ESCAPE '#'. 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME. 
ENDSELECT.

You may only place an escape symmbol before a special character ('%' and '_') or before itself.

Effect

The condition is met for a table entry if the statement "f is (not) contained in the list g1, ..., gn" is true for the values of f, g1, ..., gn. f must always be a field descriptor and g1, ..., gn an ABAP field. If f has the value NULL, the check for the statement returns unknown.

Example

Example to select all destinations served by American Airlines, Lufthansa or Singapore Airlines from Frankfurt:

TABLES SPFLI. 
DATA   TARGET LIKE SPFLI-CITYTO. 
 
SELECT DISTINCT CITYTO 
	 INTO TARGET		FROM SPFLI 
	 WHERE 
		 CARRID   IN ('AA', 'LH', 'SQ') AND 
		 CITYFROM = 'FRANKFURT'. 
  WRITE: / TARGET. 
ENDSELECT.

Note

The opening parenthesis that introduces the list, and the name g1 of the first field in the field list must not be separated by a space.

Effect

The condition is met for a table entry if the statement "f is (not) contained in the result of subquery subquery" is true for the value of f. f must always be a field descriptor and subquery a scalar subquery. If f has the value NULL, the result of the check for the statement is unknown.

TABLES SFLIGHT. 
 
SELECT * FROM SFLIGHT 
	 WHERE SEATSOCC IN ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ). 
  WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, SFLIGHT-FLDATE. 
ENDSELECT.

Note

You cannot use this variant in the ON addition of the FROM clause.

Effect

The condition is true if the contents of field f in the database table (do not) satisfy the condition stored in the internal table itab. If the statement is not true, it is false.

The internal table itab must have the structure of a RANGES tablel for f. You can create it using RANGES itab FOR f, SELECT-OPTIONS itab FOR f or DATA. If you create itab using SELECT-OPTIONS, it is automatically filled with the user defaults. Otherwise, you must fill it explicitly in your program. This is a way 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:

I Inclusive
E Exclusive

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

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

LOW When you use EQ, NE, LT, LE, GT, GE, this field contains the comparison value. When you use BT and NB, the field contains the lower limit. When you use CP and NP, the comparison value can be contained in both LOW and HIGH.

HIGH When you use BT and NB, this field contains the upper limit. When you use CP and NP, it can contain the end of the template begun in LOW.

The elementary conditions contained in itab can be combined to form a complex condition:

but not customer numbers

TABLES: SCUSTOM. 
 
SELECT-OPTIONS: R FOR SCUSTOM-ID. 
* RANGES:	 R FOR SCUSTOM-ID. 
 
* R is filled as follows (sequence of lines 
* is irrelevant: 
* 
* SIGN  OPTION  LOW	 HIGH 
* -------------------------------- 
* I	 EQ	01104711 
* I	 BT	10000000  19999999 
* I	 GE	90000000 
* E	 EQ	10000911 
* E	 BT	10000810  10000815 
* E	 CP	++++5* 
* 
* So the condition is 
* 
* ( 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 in the form f IN itab becomes a complex condition at runtime, but the maximum length of an SQL statement is restricted by the database system (for example, to 8 KB), the internal table itab should not contain too many lines.

Effect

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

Example

Example to select all customers for whom no telephone number is specified:

TABLES SCUSTOM. 
 
SELECT * FROM SCUSTOM WHERE TELEPHONE IS NULL. 
ENDSELECT.

The SAP buffer does not support this variant. Therefore, every SELECT command on a buffered table or a view with fields from buffered table that contains ... WHERE f IS [NOT] NULL behaves as though the FROM clause contained the BYPASSING BUFFER addition.

Effect

The condition is met for a table entry if the result set of the subquery subquery contains [does not contain] any lines. subquery must be a scalar subquery.

Example

Selects the flights for which at least one booking exists:

TABLES SFLIGHT. 
 
SELECT * FROM SFLIGHT AS F 
   WHERE EXISTS ( SELECT * FROM SBOOK 
					 WHERE CARRID = F~CARRID 
					 AND CONNID = F~CONNID 
					 AND FLDATE = F~FLDATE ) . 
   WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, SFLIGHT-FLDATE. 
ENDSELECT. 
 
Note

You cannot use this variant in the ON addition of the FROM clause.

Effect

NOT cond is true when cond is false. The condition is false when cond is true. This gives the following truth table:

------------------------- 
| NOT	 |		 | 
------------------------- 
| true	| false	 | 
| false	 | true	| 
| unknown   | unknown   | 
-------------------------

cond can be any condition according to WHERE variants 1 - 12. NOT is stronger than AND and OR. You can use parentheses to determine the sequence for analysis explicitly.

Note

Parentheses establishing the analysis sequence must be separated by spaces.

Example

Example to select customers with customer numbers who do not live in ZIP code area 68.

... WHERE NOT POSTCODE LIKE '68%'

Effect

cond1 AND cond2 is true if cond1 and cond2 are both true. The condition is false when either cond1 or cond2 is false. This gives the following truth table:

------------------------------------------------- 
| AND	 | true	| false	 | unknown   | 
------------------------------------------------- 
| true	| true	| false	 | unkown	| 
| false	 | false	 | false	 | false	 | 
| unknown   | unknown   | false	 | unknown   | 
-------------------------------------------------

cond1 and cond2 can be any conditions according to WHERE variants 1 - 12. AND is stronger than OR but weaker than NOT . You can determine the analysis sequence explicitly using parentheses.

Note

Parentheses determining the analysis sequence must be separated by spaces.

Example

Example to select customers with customer numbers lower than '01000000' and who do not live in ZIP code area 68:

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

Effect

cond1 OR cond2 is true when cond1 or cond2 is true. The condition is false if both cond1 and cond2 are false. This gives 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 WHERE variants 1 - 12. OR is weaker than AND and NOT . You can determine the analysis sequence dynamically using parentheses.

Note

Parentheses determining the analysis sequence must be separated by spaces.

Examples

Example to select all customers with customer numbers less than '01000000' or greater than '02000000':

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

Example to select customers with customer numbers less than '01000000' or greater than '02000000' who do not live in ZIP 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 field satisfy the conditions stored in internal table itab. itab is filled at runtime. In other words, the conditions for the field are specified dynamically.

You may only use this variant for SELECT. The internal table itab may only have one field. This must have type C , and may not be longer than 72 characters. You must specify itab in parentheses, without a space between the parentheses and the table name. The condition contained in the internal table must have the same form as a corresponding condition in the ABAP source code. The following restrictions apply:

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

- You cannot use the IN operator in the form f1 IN itab1.

Since the syntax check cannot be carried out until runtime, specifying a WHERE condition at runtime has a longer runtime than an equivalent statement in the program code.

Example

Example to display flights based on input of airline 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.