Restricted built-in functions

 

Some built-in functions are restricted in the way certain relational operators are specified on the QRYSLT and GRPSLT parameters. The following built-in function is supported only as the second operand of the 'equal' or 'not-equal' relational operators specified on the QRYSLT and GRPSLT parameters:

%NULL

%NULL accepts no arguments. It is used to select or omit records based on whether a field in the record contains a null value. Example:

OPNQRYF      FILE(library/file)
     QRYSLT('charfld = %NULL')

This query would select all the records where 'charfld' contains the null value.

The following built-in functions are supported only as the second operand of the 'equal' relational operator specified on the QRYSLT and GRPSLT parameters:

%RANGE (low-value high-value)

%RANGE is used to identify the lower and upper boundaries for the value of a field or expression. %RANGE must be specified as the right side of a relation whose operator is equal. The low-value and high-value argument must be field names, character strings, or numeric literals, to match the type of field or expression specified as left side of the relation. For example, to select only records where the numeric field NBRFLD has a value ranging from 10 through 20, specify as follows:

'nbrfld = %RANGE(10 20)'

If the low-value argument is greater than the high-value argument, the relation produces a logical value of 'false'.

%VALUES (allowed-value...)

%VALUES is used to identify a list of allowed values for a field or expression. %VALUES must be specified as the right side of a relation whose operator is equal. The allowed-value arguments must be character string or numeric literals, to match the type of the field or expression specified as the left side of the relation. For example, to select only records where the second character of field CHARFLD has a value that is one of the values 'A', 'E', 'I', 'O', or 'U', specify as follows:

'%SST(charfld 2 1) = %VALUES(''A'' ''E'' ''I'' ''O'' ''U'')'

%WLDCRD (''pattern-string'' ''wild-characters'')

%WLDCRD is used to specify a pattern that performs a wildcard scan of the character or hexadecimal field or string expression (except for expressions made up of a single character-string literal) that must be specified as the left side of the relation. %WLDCRD must be specified as the right side of a relation whose operator is equal. The pattern-string argument must be a character-string, DBCS, or graphic literal, to match the left side of the relation. The wild-characters argument is an optional parameter that specifies what 'wildcard' characters are used in the pattern-string.

If specified for character data only (no DBCS data), the wild-characters argument must be a character-string literal of exactly two characters. The first character is the value that matches any single character in the search string. The second character is the value that matches a substring of any zero or more characters. The two characters must not be the same, but there is no requirement that either character appear in the pattern-string. If the wild-characters argument is omitted, the default is for an underline ('_') to match any single character and an asterisk ('*') to match a substring of any zero or more characters.

If the wild-characters argument is specified for DBCS data only (no character data), the argument must be a double-byte character-string literal of exactly two double-byte characters. The first double-byte character is the value that will match any one double-byte character in the search string. The second double-byte character is the value that will match a substring of any zero or more characters. The two double-byte characters must not be the same, but there is no requirement that either character appear in the pattern string. If the wild-characters argument is omitted, the default is for a DBCS underline to match any one double-byte character and a DBCS asterisk to match a substring of any zero or more double-byte characters.

If the wild-characters argument is specified for both character and DBCS data, in addition to the previous rules, the argument must first contain a single-byte character-string literal (two single-byte characters), then a double-byte character string (two double-byte characters).

In this case, the first character matches any single-byte character in the character string, the second character matches a substring of any number of single-byte or double-byte characters. The first double-byte character matches any double-byte character in the character string. The second double-byte character matches a substring of any number of single-byte or double-byte characters.

The following example selects only records where the character field CHARFLD contains a 'T', followed by any two characters and an 'E', appearing anywhere in the field.

'charfld = %WLDCRD(''*T__E*'')'

The asterisks at the start and end of the pattern-string are required to allow the 'T' and 'E' to appear somewhere other than the first and last positions in the field:

To select only records where the character field CHARFLD starts with the string 'ABC', followed by one or more other characters and then followed by the string 'XYZ' (but not necessarily at the end of the field), specify the following.

'charfld = %WLDCRD(''ABC_*XYZ*'')'

To select only records where the second character of field CHARFLD is an asterisk ('*'), the last character is an underline ('_'), and the letter 'M' appears somewhere in between, specify as follows:

'charfld = %WLDCRD(''#*.M._'' ''#.'')'

 

Parent topic:

Using Open Query File (OPNQRYF) command