Syntax of WHERE clause

The command INGRDS has some restrictions with the WHERE clause in comparison to full SQL.

Restrictions

Join, subquery and aggregate functions are not supported.
Host variables are not supported.
Only a limited set of functions are supported.
REXX rules apply for expressions.

REXX Where-Clause Expression

The finally parsed and resolved WHERE-clause, resolved_where_clause, is treated as a string that will be executed by the REXX interpreter via the following REXX instruction:

if (resolved_where_clause) then
    bool=1
else
    bool=0

The REXX rules for expressions and operators apply

For example the operator AND has higher priority than OR. Also the rules for how parentheses are handled is defined by the REXX rules. Restrictions that apply due to the usage of this technique can never be resolved!

Restricted Syntax Checking

The syntax parser finds that a column name is invalid/not known, for example, "xxx='abc'":

ING338I Function or command INGRCRDS failed, RC=116 REASON=QUERY 
TABLE MYTAB Invalid column name xxx

In many cases the syntax parser detects that a quote is missing, for example, 'xxx='abc ":

ING338I Function or command INGRCRDS failed, RC=115 REASON=QUERY 
TABLE MYTAB WHERE/SET=clause parser error 46

But for many other invalid WHERE-clauses the SA syntax parser is less restrictive.

For example, if the operator is not valid or not specified at all such as "name 'abc'"(rather than " name='abc'") or "name like %abc%" (rather than "name like '%abc%'") the final expression results in a REXX error.

If such a REXX error occurs in resolved_where_clause then the message ING338I reports the error.

Error Message examples:

"'1'/2" results in:

ING338I Function or command INGRCRDS failed, RC=116 REASON=QUERY TABLE MYTAB 
No match due to bad WHERE-clause: Logical value not 0 or 1

"name like %aa%" results in:

ING338I Function or command INGRCRDS failed, RC=116 REASON=QUERY TABLE MYTAB 
No match due to bad WHERE-clause: Invalid expression

"name like abc" results in:

ING338I Function or command INGRCRDS failed, RC=116 REASON=QUERY TABLE MYTAB 
No match due to bad WHERE-clause, error near: abc

Supported Syntax

Examples:

WHERE(NAME='Bond' AND CITY='London')

The following list of expressions can be used to construct the WHERE clause:

Predicate is an expression that can be true or false and can contain either uppercase or lower case characters. You can specify the following types of predicates:
- Comparison predicates
- IN predicate
- LIKE predicate
Character function such as substring function. The substring function allows you to retrieve parts of character strings that are to be used as search criteria. The substring function and the lower/upper function may be used in conjunction with the predicates previously mentioned.
You can combine predicates and the character functions into compound Boolean Expressions. For example, you can create a WHERE clause like the following:
```
(NAME = 'BOND' and CITY <>BIRTHCITY) OR FIRST_NAME = 'James' 
AND STREET IN('Central Avenue','Lincoln Road','Catwalk')
```

Comparison Predicate

A comparison predicate uses the syntax: colname relationaloperator value

The relationaloperator can be any of the following:

Table 1. Relational Operator Listing
Relational Operator	Description
=	equal
<>	not equal
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to

Example: DESIRED_STATE <> 'down'

IN Predicate

An IN predicate compares the column name to one or more strings. The maximum number of strings is not limited. Use the following syntax to specify an IN predicate: colname IN (string1,string2,...,string20)

For example, NAME IN('Ashton','Bond','Connory','Jones','Tong')

LIKE Predicate

A LIKE predicate compares a column to a wildcard value you specify and selects columns conforming to the wildcard value. Use this syntax for a LIKE predicate: colname LIKE 'wildcard'

You must enclose the wildcard value in single quotes. A wildcard can include any combination of characters and either the percentage sign (%) or the underscore(_).

The percentage sign denotes any set of characters, including blanks.

For example, the following clause: NAME LIKE '%on%' selects from the NAME column all of the following values: Ashton,Bond,Connory,Jones,Tong

The underscore sign denotes only one character in a specific position. For each character of column data you do not want to match, the wildcard must include an underscore. For example, the following clause: NAME LIKE '_on_' selects from the NAME column that contains all of the following values: Bond, Tong

Restrictions for ESCAPE keyword

ESCAPE character cannot be specified. It is always backslash (\)
The ESCAPE keyword can be specified but will be ignored.

When the percentage sign (%) or underscore character is part of the column data, use the ESCAPE character backslash \. The ESCAPE character prevents INGRDS from interpreting the percentage sign (%) and underscore characters in column text as wildcards. For example, to find the columns containing the characters I_MS1 and I_MS2, use the following predicate: NAME LIKE 'I\_MS_'

Character Functions

The operands of a character string function can be literal character strings or column names. The following character functions are supported:

SUBSTR (argument FROM n FOR m)
UPPER (argument)
LOWER (argument)

The argument may be either a column name or a literal.

Examples:

SUBSTR (NAME FROM 3 for 2) = 'nd'

UPPER (NAME)= UPPER('bond')

The SUBSTR clause takes from the column name argument starting at position n as many characters as specified with m. For example SUBSTR (CITY FROM 5 FOR 4) returns the string 'York' if the CITY is 'New York'.

The LOWER function translates a character string literal or column value to all lower case characters.

The UPPER function translates a character string literal or column value to all upper case characters.

Example of a Complex WHERE-clause

The purpose of this WHERE-clause is simply to show how to combine predicates. It shows an example of combinations of comparison predicates, IN and LIKE predicates and character functions SUBSTR and UPPER.

table = 'sample'

/*define query*/
where_clause =,
"(NAME='Bond' AND CITY <> 'London')",
"OR ('James' <> FIRST_NAME)"
"OR NOT (CITY='Berlin')",
"AND CITY IN ('Paris','London','New York')",
"AND FIRST_NAME LIKE '%ame%'",
"AND SUBSTR (CITY FROM 4 FOR 3)='don'",
"AND UPPER (NAME)=UPPER('james')",
"AND LOWER (NAME)='james'"

/*execute query*/
'INGRDS QUERY' table 'WHERE('where_clause ') OUTPUT(STEM) STEM(out.) FORMAT(TEXT)'

/*show query output*/
if(rc=0) then do 
  do i=1 to out.0
    say out.i
  end