SELECT Comparison-Expression

Global Syntax › SELECT Comparison-Expression

SELECT Comparison-Expression

Comparison-Expression is used in the SELECT WHERE criteria clause and in the SELECT HAVING criteria clause.

Access mode: The syntax below is invalid when the access switch is set to IDMS.

Syntax:

►►─┬─ expression ─┬─┬───────┬─ IN ─┬─ data-value ─────────────┬───┬─────────┬─►◄
   │              │ └─ NOT ─┘      │     ┌────── , ─────┐     │   │         │
   │              │                └─ ( ─▼─ data-value ─┴─ ) ─┘   │         │
   │              ├─┬───────┬─ LIKe ─┬─ data-value ─────────────┬─┤         │
   │              │ └─ NOT ─┘        │     ┌────── , ─────┐     │ │         │
   │              │                  └─ ( ─▼─ data-value ─┴─ ) ─┘ │         │
   │              ├─┬─ MATches ──┬─┬─ character-string ─┬─────────┤         │
   │              │ └─ CONtains ─┘ └─ mask-value ───────┘         │         │
   │              └─┬─ = ──┬─ expression ─────────────────────────┘         │
   │                ├─ <> ─┤                                                │
   │                ├─ > ──┤                                                │
   │                ├─ < ──┤                                                │
   │                ├─ >= ─┤                                                │
   │                └─ <= ─┘                                                │
   ├─┬─ field-reference ─────┬┬─┬ = ─┬─ data-value ─┬─ TO ───┬─ data-value ┬┤
   │ └─ function-expression ─┘│ └ <> ┘              └─ THRU ─┘             ││
   │                          └─┬─────┬─ BETween data-value AND data-value ┘│
   │                            └ NOT ┘                                     │
   │                                                                        │
   └─ set-name ─┬────────────────────┬─┬────────────────────┬───────────────┘
                ├─ ,record-name ─────┤ ├─ ,record-name ─────┤
                └─ ,alt-source-name ─┘ └─ ,alt-source-name ─┘

Syntax rules:

expression

Specifies a series of constants or variables separated by operators that yields a single value.

IN data-value

Compares an expression to a data value or a list of data values:

IN— Matches an expression to a list of one or more data values. The comparison is true if the expression matches one or more of the data values.
The IN predicate is equivalent to coding a series of OR expressions. For example:
```
select * from employee
 where emp-last-name in ('jones','tanaka', 'anderson')
```
is equivalent to:
```
select * from employee
 where emp-last-name = 'jones' or
       emp-last-name = 'tanaka' or
       emp-last-name = 'anderson'
```
NOT IN— Compares a column expression to a list of one or more data values. The comparison is true if the column expression does not match any of the data values.
The NOT IN predicate is equivalent to coding a series of AND expressions. For example:
```
select * from employee
 where emp-last-name not in ('jones','tanaka','anderson')
```
is equivalent to:
```
select * from employee
 where emp-last-name <> 'jones' and
       emp-last-name <> 'tanaka' and
       emp-last-name <> 'anderson'
```
data-value— The data value or list of data values (data-value,...) to which the expression is compared. Each data value must be enclosed in quotation marks. If more than one data value is specified, the list must be enclosed in parentheses and the data values separated by commas.

LIKE data-value

Searches the expression for a data value.

LIKE— Determines whether an expression contains a data value. The comparison is true if the column expression contains the data value.
NOT LIKE— Determines whether an expression does not contain a data value. The comparison is true if the expression does not contain the data value.
Data-value— The data value to which the expression is compared. The data value can contain:
- Alphanumeric characters for an exact match
- Special characters to use as wild cards
- Escape characters to exactly match the special characters

Object String	Data Value	Example of Syntax	Example of True comparison
Underscore (┘)	Any single character	NAME LIKE 'S┘┘'	True if NAME is exactly 3 characters long and the first character is S
Percent sign (%)	Any sequence of zero or more characters	NAME LIKE '%C┘┘'	True if NAME is 3 or more characters long AND the third from last character is C
Single alphanumeric character	Exact match to that alphanumeric character	NAME LIKE 'MAC'	True if NAME is MAC
Escape character + underscore (┘)	Exact match to the underscore (┘)	PARTNUM LIKE '┘115' ESCAPE ''1	True if PARTNUM is '┘115'
Escape character + percent sign (%)	Exact match to the percent sign (%)	PARTNUM LIKE '%15' ESCAPE ''1	True if PARTNUM is '%15'
Escape character alone	Exact match to the escape character	PARTNUM LIKE '***' ESCAPE '' (note below)	True if PARTNUM is **

The escape character can be any single alphanumeric character and is set by specifying ESCAPE 'escape-character' in your SELECT statement.

MATCHES/CONTAINS

Specifies search conditions as follows:

MATCHES— A character-string or mask-value against which the named field is to be evaluated, character by character. The match must be exact, starting with the first character in the mask. The special characters that can be used for the mask are:
- Asterisk (*) specifies any character. If an asterisk is specified, the entire MATCHES string must be enclosed in quotation marks.
- At sign (@) specifies any alphabetic character.
- Pound sign (#) specifies any numeric character.
If you specify any other character, the match is for that character only. Only the left-most significant characters of the mask need be specified explicitly when the remaining characters in the field are allowed to have any value. For example, to retrieve all addresses where the first two digits of the zip code are 02, the mask value can be specified as follows:
```
'02'
```
Unspecified mask characters are treated as if any character were specified. However, if you want to test the zip code field for numeric values only, the mask must be specified as '02###'. If the specified mask value is longer than the field being checked, the extra mask characters are ignored.
CONTAINS— Specifies a character string or mask value that you want to search for. The CONTAINS value can appear anywhere in the named field. For example, the character string EL appears in FIELD, in ELEMENT, and in COMPEL.
Note: MATCHES and CONTAINS apply only to fields with a usage of DISPLAY and do not allow values that contain double-byte string characters.

= <> > < >= <=

Specifies the comparison operator:

= means Equal to
<> means Not equal to
> means Greater than
< means Less than
>= means Greater than or equal to
<= means Less than or equal to

expression

Specifies the expression the named condition is compared to.

Note: See the expansion of Expression, later in this chapter.

field-reference

Identifies a field.

Note: For more information, see the expansion of FIND / GET and COMPUTE Field-Reference Clause, later in this chapter.

function-expression

An expression containing a built-in function. Note that built-in functions can be nested.

= <> data-value

Data-value represents data values to which the named field is compared. Specifies that a column expression or column name equals (=) or doesn't equal (<>) the specified data value.

TO/THRU data-value

Specifies a range of data values to which the named field is compared. THRU indicates an inclusive range. TO indicates an exclusive range.

BETWEEN data-value and data-value

Specifies a range of data values to which the named field is compared. BETWEEN indicates that the named field meets the requirements inclusive of the boundaries specified by data-value AND data-value. NOT BETWEEN indicates that the named field doesn't meet the requirements inclusive of the boundaries specified by data-value AND data-value.

set-name

Identifies a set relationship. This sub-clause is valid in the WHERE clause only. Set-name is required.

record-name

Is the name of either the owner or member record of the set. Either the owner or member record can be specified or omitted.

alt-source-name

Specifies an alternative name used to identify records with the same name. Alt-source-name is a 1- to 8-character alphanumeric literal.

For more information:

Coding Considerations