Search Conditions

[`NOT`]	`predicate`
	(`search-condition`)
`search-condition`		`AND`	`search-condition`
		`OR`

This document covers the following topics:

Search Condition
Predicate

Search Condition

A search-condition can consist of a simple predicate or multiple search-conditions. Multiple search-conditions are combined with the Boolean operators AND, OR and NOT, and can contain parentheses if required to indicate a desired order of evaluation.

Example

DEFINE DATA LOCAL 
01 NAME   (A20) 
01 AGE    (I2) 
END-DEFINE 
... 
SELECT * 
  INTO NAME, AGE 
  FROM SQL-PERSONNEL 
  WHERE AGE = 32 AND NAME > 'K' 
END-SELECT 
...

Predicate

scalar-expression comparison

scalar-expression
subquery

scalar-expression [NOT] BETWEEN scalar-expression AND scalar-expression

scalar-expression IS [NOT] DISTINCT FROM scalar-expression

column-reference [NOT] LIKE

atom
special-register

[ESCAPE atom]

column-referenceIS [NOT] NULL

scalar-expression [NOT] IN

subquery

atom
special-register

,...

scalar-expression comparison

ALL
ANY
SOME

subquery

EXISTS subquery

XMLEXISTS (xquery-expression-constant{

BY
                                                  REF

|PASSING xquery-argument,...})

A predicate specifies a condition that can be "true", "false" or "unknown".

In a search-condition, a predicate can consist of a simple or complex comparison operation or other kinds of conditions.

Example:

SELECT NAME, AGE 
  INTO VIEW PERS 
  FROM SQL-PERSONNEL 
  WHERE AGE BETWEEN 20 AND 30    
    0R AGE IN ( 32, 34, 36 ) 
    AND NAME LIKE '%er' 
    ...

Note:
The percent sign (%) may conflict with Natural terminal commands. If so, you must define a terminal command control character different from %; see Changing the Terminal Command Control Character in the Terminal Commands documentation.

The individual predicates are explained in the following topics (for further information on predicates, please refer to the relevant literature). According to the syntax above, they are called as follows:

Comparison Predicate
BETWEEN Predicate
DISTINCT Predicate
LIKE Predicate
NULL Predicate
IN Predicate
Quantified Predicate
EXISTS Predicate
XMLEXISTS Predicate

Comparison Predicate

	`scalar-expression`	`comparison`			`scalar-expression`
	`row-value-expression`	`comparison`			`row-value-expression`

A comparison predicate compares two values or a set of values with another set of values.

In the syntax diagram above, comparison can be one of the following operators:

`=`	equal to
`<`	less than
`>`	greater than
`<=`	less than or equal to
`>=`	greater than or equal to
`<>`	not equal to

See information on scalar-expression.

Subquery

(select-expression)

A subquery is a select-expression that is nested inside another such expression.

Example:

DEFINE DATA LOCAL 
1 #NAME    (A20) 
1 #PERSNR  (I4) 
END-DEFINE 
... 
SELECT NAME, PERSNR 
  INTO #NAME, #PERSNR 
  FROM SQL-PERSONNEL 
  WHERE PERSNR IN 
    ( SELECT PERSNR 
        FROM SQL-AUTOMOBILES 
        WHERE COLOR = 'black' )  
        ... 
END-SELECT

For further information, see Select Expressions.

BETWEEN Predicate

scalar-expression [NOT] BETWEEN scalar-expression AND scalar-expression

A BETWEEN predicate compares a value with a range of values.

See information on scalar-expression.

DISTINCT Predicate

	`scalar-expression` `IS` [`NOT`] `DISTINCT FROM` `scalar-expression`
	`row-value-expression` `IS` [`NOT`] `DISTINCT FROM` `row-value-expression`

A DISTINCT predicate compares a value with another value or a set of values with another set of values.

LIKE Predicate

`column-reference` [`NOT`] `LIKE`		`atom`		[`ESCAPE` `atom`]
		`special-register`

A LIKE predicate searches for strings that have a certain pattern.

See information on column-reference, atom and special-register.

NULL Predicate

`column-reference`		`IS` [`NOT`] `NULL`
		`ISNULL`
		`NOTNULL`

A NULL predicate tests for null values.

If the compiler option DB2ARRY is set to ON, it is possible to specify an Natural array or an fixed index range of an array as atom. The Natural SQL compiler will then decompose the array or fixed index range into a list of scalar host variables.

See information on column-reference.

IN Predicate

	`scalar-expression` [`NOT`] `IN`		`subquery`
			`row-value-expression`
	`row-value-expression` [`NOT`] `IN` `subquery`

An IN predicate compares a value or a set of values with a collection of values.

See information on scalar-expression, atom and special-register.

See information on subquery.

Quantified Predicate

`scalar-expression`	`comparison`		`SOME` `ANY` `ALL`	`subquery`
`row-value-expression`	`=`		`SOME` `ANY`	`subquery`
`row-value-expression`	`<>`	`ALL`		`subquery`

A quantified predicate compares a value or a set of values with a collection of values.

See information on scalar-expression, comparison and subquery.

EXISTS Predicate

EXISTS subquery

An EXISTS predicate tests for the existence of certain rows.

The EXISTS predicate evaluates to true only if the result of evaluating the subquery is not empty; that is, if there exists at least one record (row) in the FROM table of the subquery satisfying the search condition of the WHERE clause of this subquery.

Example of EXISTS:

DEFINE DATA LOCAL 
1 #NAME    (A20) 
END-DEFINE 
... 
SELECT NAME 
  INTO #NAME 
  FROM SQL-PERSONNEL 
  WHERE EXISTS 
    ( SELECT * 
      FROM SQL-EMPLOYEES 
      WHERE PERSNR > 1000  
        AND NAME < 'L' ) 
        ... 
END-SELECT 
...

See information on subquery.

XMLEXISTS Predicate

`XMLEXISTS` `(xquery-expression-constant`		`BY REF`		`)`
		`PASSING` `xquery-argument`,...

`xquery-argument`

	`xquery-context-item-expression`
	`xquery-context-item-expression` `AS` `identifier`

The XMLEXISTS predicate belongs to the Natural SQL Extended Set.

The XMLEXISTS predicate tests whether an XPATH expression returns a sequence of one or more items. For further information, see the IBM Db2 XML Guide.