2.7.5 Predicates

Organization of this subsection
(1) Comparison predicate
(2) NULL predicate
(3) IN predicate
(4) LIKE predicate
(5) XLIKE predicate
(6) SIMILAR predicate
(7) BETWEEN predicate
(8) Quantified predicate
(9) EXISTS predicate
(10) Boolean predicate
(11) Structured repetition predicate

(1) Comparison predicate

Format

row-value-constructor
{= | <> | ^= | != | < | <= | > | >=} row-value-constructor

Case in which the predicate is TRUE
Rules
  1. On both sides of the comparison operators (=, <>, ^=, !=, <, <=, >, >=), row value constructors consisting solely of literals can be specified.
  2. Values that yield any of the following data types as a result of an operation cannot be specified:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
  3. For row value constructors, see 2.8 Row value constructors.
  4. Row value constructor elements that are located in the corresponding positions in the right and left row value constructors are treated as corresponding values. The corresponding values must have data types that are comparable.

    [Figure]

  5. A subquery cannot be specified in a comparison predicate in any of the following locations:
    • A search condition in an IF statement
    • A search condition in a WHILE statement
    • A WHEN search condition in CREATE TRIGGER (trigger action search condition)
  6. When specifying a repetition column, specify a subscript. If a subscripted repetition column is specified and if its elements satisfy the condition, the result of the comparison with the corresponding value is TRUE.
  7. The word ANY can be specified as a repetition column subscript. If ANY is specified and if at least one element in the column satisfies the condition, the result of the comparison is TRUE. If the result of the comparison is not TRUE and if the condition specified on at least one element of the column is indefinite, the result of the comparison is indefinite. If the result of the comparison is not TRUE or indefinite, the result of the comparison is FALSE.
  8. If a subscripted repetition column is specified and the subscripted elements do not exist, the result of the comparison with the corresponding value is indefinite.
  9. The row value constructors that are to be compared must have the same number of result columns.

(2) NULL predicate

Format

item-specification IS [NOT] NULL

Conditions under which a predicate is TRUE

For a row that contains the NULL value in specified item (column, component specification, SQL variable, or SQL parameter), the NULL predicate will be TRUE. If NOT is specified, the NULL predicate will be TRUE for a row that does not contain the NULL value. For details of the NULL value, see 1.6 Null value.

Rules
  1. The NULL predicate can be specified in the following locations:
    • ON search condition in a FROM clause
    • WHERE clause
    • Search condition of an IF statement
    • Search condition of a WHILE statement
    • WHEN search condition of a searched CASE expression (excluding a searched CASE expression directly specified in a HAVING clause)
    • WHEN search condition of CREATE TRIGGER (trigger action condition)
  2. Items with any of the following data types cannot be specified:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
  3. If an unsubscripted repetition column is specified and the column does not contain any elements (in the case of the NOT specification, if at least one element is present), the NULL predicate will be TRUE. Even if all the elements in the column are NULL, the NULL predicate will not be TRUE.
  4. If a subscripted repetition column is specified and a specified element is NULL, the NULL predicate will be TRUE.
  5. If a subscripted repetition column is specified and the column does not contain any elements, the NULL predicate will be UNKNOWN.
  6. When a subscripted repetition column is specified, ANY can be specified as a subscript. When ANY is specified and at least one element in the column satisfies the specified conditions, the NULL predicate will be TRUE.

(3) IN predicate

Format

row-value-constructor [IS] [NOT] IN
{(row-value-constructor [,row-value-constructor]...)
|(table-subquery)
([SQL-optimization-specification-for-subquery-execution-method]
SELECT[{ALL | DISTINCT}]{selection-expression|*}
(table-expression)
FROM table-reference [,table-reference]...
[WHERE search-condition]
[GROUP BY value-expression [,value-expression]...]
[HAVING search-condition])}

Conditions under which a predicate is TRUE

The IN predicate is TRUE if any of the following conditions is satisfied:

If NOT is specified, the IN predicate is TRUE with respect to rows for which the row value constructor on the left-hand side does not match any of the result rows for any row value constructor or table subquery that is specified on the right-hand side.

Rules
  1. Values in which the data items as a result of a row value constructor or table subquery take any of the following data types:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
  2. A maximum of 255 row value constructors can be specified on the right-hand side.
  3. A row value constructor consisting solely of a value specification cannot be specified on the left side of an IN predicate that is not a table subquery.
  4. For table subqueries, see 2.4 Subqueries.
  5. Some IN predicates have the same meaning as a quantified predicate. The following predicates are synonymous:
    IN predicateQuantified predicate
    row-value-constructor IN table-subqueryrow-value-constructor = ANY table-subquery
    or
    row-value-constructor = SOME table-subquery
    row-value-constructor NOT IN table-subqueryrow-value-constructor <> ALL table-subquery
  6. If the result of a table subquery is the empty set, the result of the IN predicate is FALSE; the result, however, is TRUE if NOT is specified.
  7. A table subquery cannot be specified in an IN predicate in the following locations:
    • Search condition of an IF statement
    • Search condition of a WHILE statement
    • WHEN search condition (trigger action condition) of CREATE TRIGGER
  8. In the row value constructors, the row value constructor elements and the selection expressions of table subqueries in the corresponding positions are treated as corresponding values. The corresponding values must have mutually convertible or comparable data types. If, however, the result data type of a row value constructor element in a row value constructor specified on the left-hand side is national character data, and a character string literal is specified as the corresponding value, the character string literal is treated as a national character string literal. If a character string literal is treated as a national character string literal, HiRDB only checks the length of the character data without checking the character code.

    [Figure]

  9. When specifying a repetition column, a subscript must be specified. If a subscripted repetition column is specified and its elements satisfy specified conditions, the IN predicate is TRUE.
  10. The word ANY can be specified as a subscript for a repetition column. If ANY is specified and if at least one element in the column satisfies the specified condition, the IN predicate is TRUE. If the IN predicate is not TRUE and if a condition specified with respect to at least one element in the column is indefinite, the IN predicate becomes indefinite. Any IN predicate that is not TRUE or that is not indefinite is FALSE.
  11. A repetition column with an ANY subscript cannot be specified in the row value constructor on the right side.
  12. If a repetition column is specified with a subscript and the column contains no elements, the IN predicate is indefinite.
  13. If the result of a row value constructor is the null value, the result of the comparison of the corresponding value is indefinite.
  14. The row value constructors that are subject to comparison must have the same number of result columns.
Note
  1. If a table subquery is specified in an IN predicate, in some cases HiRDB creates a work table. In this process, the processing of the subquery in the IN predicate may be subject to restrictions depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 8 Installation and Design Guide.

(4) LIKE predicate

Format

value-expression [NOT] LIKE pattern-character-string [ESCAPE
  escape-character]

Conditions under which a predicate is TRUE

The LIKE predicate is TRUE for a row in which the value of a specified value expression matches the pattern represented by a pattern character string. If NOT is specified, the LIKE predicate is TRUE for those rows for which the value of the specified column does not match the pattern expressed by the pattern character string.

Rules

The LIKE predicate can be specified in the following locations:

value-expression
  1. Specifies the value expression that will be the object of a character string pattern comparison.
    However, value expressions in which only values other than SQL variables or SQL parameters are specified cannot be specified.
  2. The following data types can be specified in a value expression or a pattern character string: character string data, national character string data, mixed character string data, or BINARY with a maximum definition length of 32,000 bytes.
  3. When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the LIKE predicate will be TRUE.
  4. ANY can be specified as a subscript for a repetition column. When ANY is specified and at least one element in the column meets specified conditions, the LIKE predicate will be TRUE. If the LIKE predicate is not TRUE and a condition specified for at least one element in the column is UNKOWN, the LIKE predicate will be UNKNOWN. If the LIKE predicate is neither TRUE nor UNKNOWN, it will be FALSE.
  5. If a repetition column is specified with a subscript and the column contains no elements, the LIKE predicate will be UNKNOWN.
pattern-character-string
  1. Specifies a value specification.
  2. A specifiable pattern character string must have the same data type as the data type of the value expression.
  3. The following combinations of value expression data types and pattern character string data types are allowed:
    Value expression data typePattern character string data type
    Character string dataNational character string dataMixed character string dataBinary data
    Character string dataYNYN
    National character string dataN1YNN
    Mixed character string dataYNYN
    Binary dataY2NNY
    Y: Specifiable
    N: Not specifiable
    1 Only character string literals can be specified in pattern character strings. In such a case, the character string literals are treated as national character string literals. When a character string literal is treated as a national character string literal, only the character data length is checked; the character codes are not checked. Special characters must be specified as 2-byte characters.
    2 Only hexadecimal character string literals can be specified.
  4. The following special characters can be used in a pattern character string: the underline, the percent sign, and escape characters. For special characters in terms of binary data, you need to specify _, %, and a code that denotes the escape character. When used in a pattern character string, the underline and the percent sign have the meanings given in Table 2-1.

    Table 2-1 Meanings of special characters in pattern character strings (LIKE predicate)

    Item specification data typeSpecial characters
    Character string data
    • _ (underline): Any 1-byte character
    • % (percent sign): Any character string with 0 or more characters
    The underline and percent sign characters are expressed as one-byte characters.
    National character string data
    • _ (underline): Any 1-byte character
    • % (percent sign): Any national character string with 0 or more characters
    The underline and percent sign characters are expressed as two-byte characters.
    Mixed character string data
    • _ (underline): Any 1-byte character
    • % (percent sign): Any mixed character string with 0 or more characters
    The underline and percent sign characters are expressed as one-byte characters.
    Binary data
    • 5f (the code that denotes an underline)*: any 1 byte
    • 25 (the code that denotes the percent sign)*: any byte string with any number of bytes greater than or equal to 0
    * When specifying a special character using binary data, you need to specify a code that denotes a special character (_, %) and is in the character code that was defined when HiRDB was set up.
  5. If the pattern character string does not contain the % sign and the data lengths of the column to be compared and the pattern character string are different, the LIKE predicate is not TRUE.
  6. If the character string and the pattern character string specified in value-expression are variable-length data (VARCHAR, NVARCHAR, MVARCHAR, or BINARY), HiRDB compares the data lengths in addition to the data and the pattern character string data in the value-expression.
  7. When an embedded variable, an SQL variable, or an SQL parameter is specified as a pattern character string point, the following must be observed:
    If a pattern character string formed with a fixed-length embedded variable, SQL variable, or SQL parameter and if a pattern character string shorter than the length of the variable is set in that variable, the variable may be filled with trailing spaces or with any remaining invalid characters as values. If a retrieval is attempted using this type of pattern character string, target data that does not have matching trailing spaces or matching leftover characters will not be retrieved.
    To avoid this problem when a fixed-length variable is used as a pattern character string, the percent sign should be set at the end of the variable.
Example:
In the following example, character string data containing 'ABCD' gives the indicated results if pattern character strings 'AB%' and 'AB%%' are assigned to variables:
Data type of variablePattern character stringCharacter string dataResult of comparison
Variable-length character string'AB%''ABCD'Matches.
'AB%%''ABCD'Matches.
Fixed-length character string (4 bytes)'AB%[Figure]''ABCD'Does not match.
'AB%%''ABCD'Matches.
Example:
A comparison between binary data 52454452554d and a variable in which a pattern character string, X'52454425', is set gives the following results:
Data type of variablePattern character stringBinary dataResult of comparison
BINARY typeX'52454425​'52454452554​dMatches
X'5245442525​'52454452554​dMatches

Examples of pattern character strings

Table 2-2 shows typical pattern character strings used in the LIKE predicate.

Table 2-2 Typical pattern character strings used in the LIKE predicate

ItemPattern character stringMeaningExample
Pattern character stringPattern-matching character string
Front matchnnn%The leading portion of the character string is nnn.'ACT%'Character strings beginning with "ACT", such as ACT, ACTOR, and ACTION.
Rear match1%nnnThe trailing portion of the character string is nnn.'%ING'Character strings ending with"ING", such as ING, BEING, and HAVING.
Any match%nnn%The character string contains nnn at any position.N'%OR%'Character strings containing "or", such as OR, More, and CoLoR.1
Complete matchnnnThe character string is equal to nnn.'EQUAL'EQUAL
Partial match_..._nnn_..._ I jA specific portion (from ith to jth character) of the character string is equal to nnn; the other portions of the character string contain any characters.'_I_'3-letter character strings, in which the second character is "I", such as BIT, HIT, and KIT.
Othernnn%mmmThe leading portion of the character string is nnn and the trailing portion is mmm.'O%N'Character strings that begin with "O" and end with "N"" such as ON, OWN, and ORIGIN.
%nnn%mmm%The character string contains nnn at any position and mmm at a subsequent position.'%O%N%'Character strings that contain "O," and have an "N" in any subsequent position, such as ON, ONE, DOWN, and COUNT.
nnn_..._ 1 i mmm% j kFrom the first to the ith character is nnn; from the jth to the kth character is mmm.'CO_ _ ECT%'Character strings that begin with "CO" and contain the string "ECT" in the 5th through 7th character positions, such as CORRECT, CONNECTER, and CONNECTION

1 Because the space is regarded as a character for comparison purposes, a comparison with data that has trailing spaces produces the FALSE result.

2 In a national character string, the _ and % special characters are coded using the _ and % national characters.

Note
nnn and mmm denote any character strings that do not contain % or _.
escape character
Any underline or percent sign coded in a pattern character string is unconditionally treated as a special character; these characters cannot be treated as regular characters. When a special character is to be specified as a regular character, an escape character must be specified. Specifying any character after the ESCAPE keyword (an escape character) causes the special character following the escape character coded in the pattern character string to be treated as a regular character.
Example 1
A character string containing a '5%', such as '5%' and '25%':
'%5?%%' ESCAPE '?'
Example 2
A character string ending with 'PRINT_REC', such as 'SQLPRINT_REC':
'%PRINT@_REC' ESCAPE '@'
Example 3
A hexadecimal character string containing X'48695244425f' in the binary string, such as X'48695244425f':
X'4869524442ee5f' ESCAPE X'ee'
The following characters can be specified as escape characters:
Item data typeSpecifiable character
Character data (CHAR, VARCHAR)Any one-byte character
Mixed character data (MCHAR, MVARCHAR)
National character data (NCHAR, NVARCHAR)Any two-byte character
Binary data (BINARY)Any single-byte value
Note
Care must be taken that the special character is specified following the escape character.
Notes
  1. For improved performance, the definition length of the column used in a value expression on the left-hand side of LIKE should be either a maximum of 255 bytes (CHAR, VARCHAR, MCHAR, MVARCHAR, or BINARY) or a maximum of 127 characters (NCHAR or NVARCHAR).
  2. Multi-byte characters stored in a CHAR or VARCHAR-type column are evaluated byte by byte. Consequently, if the character code for a single-byte character specified in a pattern character string is included in the character codes for the multi-byte characters, the result of LIKE predicate is true.
    Example
    Execute the following query in a condition where the query is set up in sjis character code, a CHAR-type column C1 is in Table T1, and a row [Figure] is in column C1:
    SELECT C1 FROM T1 WHERE C1 LIKE '%A%' ;
    The character code for the character [Figure] in hexadecimal is 8341. The character code in hexadecimal for the character A in the pattern character string is 41. Therefore, because the character code for [Figure], which is a multi-byte character, includes the character code for the single-byte character A, the result of the LIKE predicate is true.

(5) XLIKE predicate

Format

value-expression [NOT] XLIKE pattern-character-string [ESCAPE escape-character]

Conditions under which a predicate is TRUE

The XLIKE predicate is TRUE for a row in which the value of a specified value expression matches the pattern represented by a pattern character string. If NOT is specified, the predicate is TRUE for a rows for which the value does not match the pattern character string. The comparison performed is not case-sensitive.

Rules

The XLIKE predicate can be specified in the following locations:

value-expression
  1. Specifies the value expression that will be the object of a character string pattern comparison.
    However, value expressions in which only values other than SQL variables or SQL parameters are specified cannot be specified.
  2. Character string data, national character data, or mixed character string data can be specified as the data type of a value expression.
  3. When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the XLIKE predicate will be TRUE.
  4. ANY can be specified as a subscript for a repetition column. If ANY is specified and at least one element in the column meets specified conditions, the XLIKE predicate will be TRUE. If the XLIKE predicate is not TRUE and a condition specified for at least one element in the column is UNKOWN, the XLIKE predicate will be UNKNOWN. If the XLIKE predicate is neither TRUE nor UNKNOWN, it will be FALSE.
  5. If a repetition column is specified with a subscript and the column contains no elements, the XLIKE predicate will be UNKNOWN.
pattern-character-string
  1. A value specification must be specified in the pattern character string.
  2. Any data type that can be specified in an value expression can be specified in a pattern character string.
  3. The following combinations of value expression data types and pattern character string data types are allowed:
    Value expression data typePattern character string data type
    Character string dataNational character string dataMixed character string data
    Character string dataYNY
    National character string dataN*YN
    Mixed character string dataYNY
    Y: Specifiable
    N: Not specifiable
    * Only character string literals can be specified in pattern character strings. In such a case, the character string literals are treated as national character string literals. When a character string literal is treated as a national character string literal, only the character data length is checked; the character codes are not checked. Special characters must be specified as 2-byte characters.
  4. The following special characters can be used in a pattern character string: the underline, the percent sign, and escape characters. When used in a pattern character string, the underline and the percent sign have the meanings given in Table 2-3.

    Table 2-3 Meanings of special characters in pattern character strings (XLIKE predicate)

    Item specification data typeSpecial characters
    Character string data
    • _ (underline): Any 1-byte character
    • % (percent sign): Any character string with 0 or more characters
    The characters underline and percent sign are expressed as one-byte characters.
    National character string data
    • _ (underline): Any 1-byte character
    • % (percent sign): Any national character string with 0 or more characters
    The characters underline and percent sign are expressed as two-byte characters.
    Mixed character string data
    • _ (underline): Any 1-byte character
    • % (percent sign): Any mixed character string with 0 or more characters
    The characters underline and percent sign are expressed as one-byte characters.
  5. Comparison of the following characters with pattern character string data is not case-sensitive:
    Alphanumeric national characters and mixed characters
  6. This predicate is not TRUE if a percent sign does not occur in the pattern character string or the column data and the pattern character string differ in length.
  7. If the character string and pattern character string specified in the value expression are both variable-length character strings (VARCHAR, NVARCHAR, or MVARCHAR), HiRDB compares the character string lengths, as well as the character string data and the pattern character string data.
  8. When an embedded variable, an SQL variable, or an SQL parameter is to be specified as a pattern character string, the following point must be observed:
    If a pattern character string is formed with a fixed-length embedded variable, SQL variable, or SQL parameter and if a pattern character string shorter than the length of the variable is set in that variable, the variable may be filled with trailing spaces or with any remaining invalid characters as values. When this type of pattern character string is used as a search string, data that does not similarly contain trailing spaces or invalid characters cannot be retrieved. Therefore, when using a fixed-length variable as a pattern character string, the variable must be filled with trailing percent signs.
Examples of pattern character strings

Table 2-4 shows typical examples of pattern character strings used in the XLIKE predicate.

Table 2-4 Examples of pattern character strings used in the XLIKE predicate

ItemPattern
character string
MeaningExample
Pattern character stringPattern-matching character string
Front matchnnn%The leading portion of the character string is nnn.'ACT%'Character strings beginning with "ACT"1, such as ACT, Actor, and action.
Rear match%nnnThe trailing portion of the character string is nnn.'%ING'Character strings ending with "ING"2, such as Ing, Being, and HAVING.
Any match%nnn%The character string contains nnn at any position.'%OR%'Character strings containing "or"3, such as OR, More, and CoLoR.
Complete matchnnnThe character string is equal to nnn.'MAX'Character strings such as MAX, max, and mAx4
Partial match_..._nnn_..._ i jA specific portion (from the ith through the jth character) of the character string is equal to nnn, the other portions of the character string contain any characters.'_I_'A three-character character string in which the second character is either "I" or "i", such as Bit, HIT, and Kit.
Othernnn%mmmThe leading portion of the character string is nnn and the trailing portion is mmm.'O%N'Character strings that begin with "O" or "o" and end with "N" or "n", such as on, Own, and ORIGIN.
%nnn%mmm%The character string contains nnn at any position and mmm at a subsequent position.'%O%N%'Character strings that contain "O" or "o" and have "N" or "n" at a subsequent position, such as ON, one, DowN, and Count.
nnn_..._ 1 I mmm% j kFrom the first through the ith character is nnn; from the jth through the kth character is mmm.'CO_ _ ECT%'Character strings that begin with "CO"5, and whose 5th through the 7th characters are "ECT"6, such as correct, Connecter, and CONNECTION.

Note 1: nnn and mmm are any character strings that do not contain % or _.

Note 2: Because the space character is also used as a comparison character, comparison is with data containing trailing spaces, this string yields the FALSE result.

Note 3: In a national character string, the special characters (% and _) should be coded as the national character "%" or "_".

1 One of the character strings ACT, ACt, Act, aCT, aCt, acT, or act

2 One of the character strings ING, INg, Ing, InG, iNG, iNg, inG, or ing

3 One of the character strings OR, Or, oR, or or

4 One of the character strings MAX, MAx, Max, MaX, mAX, mAx, maX, or max

5 One of the character strings CO, Co, cO, or co

6 One of the character strings ECT, ECt, Ect, EcT, eCT, eCt, ecT, or ect

Escape-character
Any underline or percent sign coded in a pattern character string is unconditionally treated as a special character; these characters cannot be treated as regular characters. When a special character is to be specified as a regular character, an escape character must be specified. Specifying any character after the ESCAPE keyword (escape character) causes the special character following the escape character coded in the pattern character string to be treated as a regular character.
Example 1
A character string containing a '5%', such as '5%' and '25%':
'%5?%%' ESCAPE '?'
Example 2
A character string ending with 'PRINT_REC', such as 'SQLPRINT_REC':
'%PRINT@_REC' ESCAPE '@'
The following characters can be specified as escape characters:
Item data typeSpecifiable character
Character data (CHAR, VARCHAR)Any one-byte character
Mixed character data (MCHAR, MVARCHAR)
National character data (NCHAR, NVARCHAR)Any two-byte character
Note
Care must be taken that the special character is specified following the escape character.
Notes
  1. For improved performance, the definition length of the column used in a value expression on the left-hand side of XLIKE should be either a maximum of 255 bytes (CHAR, VARCHAR, MCHAR, or MVARCHAR) or a maximum of 127 characters (NCHAR or NVARCHAR).
  2. Multi-byte characters stored in a CHAR or VARCHAR-type column are evaluated byte by byte. Consequently, if the character code for a single-byte character specified in a pattern character string is included in the character codes for the multi-byte characters, the result of the XLIKE predicate is true.
Example
Execute the following query in a condition where the query is set up in sjis character code, a CHAR-type column C1 is in Table T1, and a row [Figure] is in column C1:
SELECT C1 FROM T1 WHERE C1 XLIKE '%A%' ;
The character code for the character [Figure] in hexadecimal is 8341. The character code in hexadecimal for the character A in the pattern character string is 41. Therefore, because the character code for [Figure], which is a multi-byte character, includes the character code for the single-byte character A, the result of the XLIKE predicate is true.

(6) SIMILAR predicate

Format

value-expression [NOT] SIMILAR-TO-pattern-character-string [ESCAPE escape-character]

Conditions under which a predicate is TRUE

The SIMILAR predicate is TRUE for a row in which the value of a specified value expression matches the pattern expressed by a pattern character string. If NOT is specified, the predicate is TRUE for a row in which the value of a specified value expression does not match the pattern expressed by the pattern character string. Note that when the length of the pattern character string is 0, the SIMILAR predicate is TRUE when the length of the value expression is 0.

Rules
value-expression
  1. Specifies the value expression that is to be compared with the character string pattern. However, you cannot specify a value expression that specifies only the ? parameter, or a value of an embedded variable.
  2. The following data types can be specified in a value expression or a pattern character string: character string data, national character string data, mixed character string data, or BINARY with a maximum definition length of 32,000 bytes.
  3. When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the SIMILAR predicate will be TRUE.
  4. ANY can be specified as a subscript for a repetition column. When ANY is specified, the SIMILAR predicate will be TRUE as long as at least one of the elements of the column satisfies the condition. If the SIMILAR predicate is not TRUE and if the condition specified for at least one of the elements of the column is unknown, the SIMILAR predicate will be FALSE. When the SIMILAR predicate is neither TRUE nor unknown, it is FALSE.
  5. If a repetition column is specified with a subscript and the column contains no elements, the SIMILAR predicate will be unknown.
pattern-character-string
  1. A value expression must be specified in the pattern character string.
  2. You cannot specify a repetition column for a value expression.
  3. The following combinations of value expression data types and pattern character string data types are allowed:
    Value expression data typePattern character string or escape character data type
    Character string dataNational character string dataMixed character string dataBinary data
    Character string dataYNYN
    National character string dataY#1YNN
    Mixed character string dataYNYN
    Binary dataY#2NNY
    Legend:
    Y: Specifiable
    N: Not specifiable
    #1
    Only character string literals can be specified in pattern character strings. In such a case, the character string literals are treated as national character string literals. When a character string literal is treated as a national character string literal, only the character data length is checked; the character codes are not checked. Special characters must be specified as 2-byte characters.
    #2
    Only hexadecimal character string literals can be specified.

  4. The format of a regular expression to be specified for a pattern character string is shown below:

    regular-expression ::= normal-term | regular-expression | regular-expression
    normal-term ::= normal-factor | normal-term normal-factor
    normal-factor ::=   normal-primary
                  | normal-primary *
                  | normal-primary +
                  | normal-primary ?
                  | normal-primary repetition-factor
    repetition-factor ::= { lower-limit [ upper-limit-specification ] }
    upper-limit-specification ::= , [ upper-limit ]
    normal-primary ::=   character-specifier
                    | %
                    | normal-character-set
                    | normal-character-set-identifier-specification
                    | ( regular-expression )
    character-specifier ::=   non-escape-character
                    | escape-character
    normal-character-set ::=   _
                      | [ character-list... ]
                      | [ ^ character-list... ]
    character-list ::=   character-specifier
                  | character-specifier - character-specifier
                  | normal-character-set-identifier-specification
    normal-character-set-identifier-specification ::= [ : normal-character-set-identifier : ]

  5. The syntax rules for a regular expression to be specified for a pattern character string are described below:
    [Figure]Specify one of the following for the normal character set identifier:
    'ALPHA', 'UPPER', 'LOWER', 'DIGIT', 'ALNUM', 'SPACE', 'WHITESPACE'
    [Figure] The non-escaped characters include all the individual characters other than the following special characters:
    Special characterCode to be specified in binary data
    _(underscore)X'5F'
    % (percent sign)X'25'
    * (asterisk)X'2A'
    + (plus sign)X'2B'
    ? (question mark)X'3F'
    | (vertical bar)X'7C'
    ( (left parenthesis)X'28'
    ) (right parenthesis)X'29'
    { (left curly bracket)X'7B'
    } (right curly bracket)X'7D'
    [ (left square bracket)X'5B'
    ] (right square bracket)X'5D'
    Escape characterValue specified in ESCAPE
    - (minus sign)#X'2D'
    : (colon)#X'3A'
    ^ (caret)#X'5E'
    #: These symbols are treated as special characters only within a character string.
    [Figure]To specify a special character as a regular character (that is, to escape it), you must specify it following the escape character.
    [Figure]For the lower and upper limits, specify integers that satisfy the following condition: 0 [Figure] lower limit [Figure] upper limit [Figure]256.
  6. Table 2-5 shows the meaning of each regular expression specification to be specified for the pattern character string.

    Table 2-5 Meaning of each regular expression specification

    Regular expression specificationMeaning
    Character specifierMeans a character (character string with a length of 1) specified by a character specifier.
    _ (underscore)Means a character with a length of 1.
    % (percent sign)Means a character string with a length of 0 or greater.
    Normal primary*Means 0 or more repetitions of the preceding normal primary.
    Normal primary+Means one or more repetitions of the preceding normal primary.
    Normal primary?Means 0 or one repetition of the preceding normal primary.
    regular-expression|regular-expressionMeans either of the regular expressions specified before and after the vertical bar (|).
    (regular-expression)Means grouping of the regular expression specified within the parentheses.
    When a regular expression is to be used, this specification is used to clarify that it is a regular expression. It is used primarily when the vertical bar (|) is used.
    normal-primary{n}
    normal-primary{n,m}
    normal-primary{n,}
    Means that the preceding normal primary is repeated. The following shows how a repetition count is specified:
    {n}: Repeats the preceding regular expression n times.
    {n,m}: Repeats the preceding regular expression at least n times but not more than m times.
    {n,}: Repeats the preceding regular expression at least n times.
    [character-list...]Means any of the characters listed.
    [^character-list...]Means any character except those listed.
    character-specifier-1-character-specifier-2When specified in a character string list, means any character between the character indicated by character-specifier-1 and the character indicated by character-specifier-2 (character code range).
    [:ALPHA:]Any upper-case alphabetic character (excluding \, @, and #) or lower-case alphabetic character
    [:UPPER:]Any upper-case alphabetic character (excluding \, @, and #)
    [:LOWER:]Any lower-case alphabetic character
    [:DIGIT:]Any numeric digit
    [:ALNUM:]Any upper-case alphabetic character (excluding \, @, and #), lower-case alphabetic character, or numeric digit
    [:SPACE:]A single-byte space (double-byte space when the value expression is national character string data)
    [:WHITESPACE:]Any single character from among the characters whose character codes are shown in the table below (what is meant by [:WHITESPACE:] depends on the type of character codes)
    The following table shows the character codes of the characters included in [:WHITESPACE:]:
    Unicode (UTF-8)Shift_JIS kanji characterEUC Japanese kanji characterEUC Chiense kanji characterLANG-CName of the character specified by Unicode rules
    X'09'X'09'X'09'X'09'X'09'Horizontal Tabulation
    X'0A'X'0A'X'0A'X'0A'X'0A'Line Feed
    X'0BX'0BX'0BX'0BX'0BVertical Tabulation
    X'0C'X'0C'X'0C'X'0C'X'0C'Form Feed
    X'0D'X'0D'X'0D'X'0D'X'0D'Carriage Return
    X'20'X'20'X'20'X'20'X'20'Space
    X'C285'--------Next Line#
    X'C2A0'--------No-Break Space#
    X'E19A80'--------Ogham Space Mark#
    X'E28080'--------En Quad#
    X'E28081'--------Em Quad#
    X'E28082'--------En Space#
    X'E28083'--------Em Space#
    X'E28084'--------Three-Per-Em Space#
    X'E28085'--------Four-Per-Em Space#
    X'E28086'--------Six-Per-Em Space#
    X'E28087'--------Figure Space#
    X'E28088'--------Punctuation Space#
    X'E28089'--------Thin Space#
    X'E2808A'--------Hair Space#
    X'E280A8'--------Line Separator#
    X'E280A9'--------Paragraph Separator#
    X'E280AF'--------Narrow No-Break Space#
    X'E38080'0x8140X'A1A1'X'A1A1'--Ideographic Space#
    Legend:
    --: Not applicable
    #
    Not included in [:WHITESPACE:] when the value expression is character string type.

  7. Binary data cannot be specified for a normal character set identifier.
  8. You should note the following about specifying an embedded variable, SQL variable, or SQL parameter as a pattern character string:
    When a fixed-length embedded variable, SQL variable, or SQL parameter is specified as a pattern character string, setting a pattern character string that is shorter than the length of the variable may cause spaces to be entered following the variable, or may set the remaining invalid characters as a value. When such a pattern character string is used for a search, data that is not followed by similar spaces or that does not contain the same value as the invalid characters is not retrieved. Therefore, when using a fixed-length variable as a pattern character string, you should fill the excess positions with the percent sign (%).
    Example 1:
    The table below shows the comparison results when AB% and AB%% are specified for the pattern character string and the character string data is ABCD.
    Variable data typePattern character stringCharacter string dataComparison result
    Variable-length character string'AB%''ABCD'Matches.
    'AB%%''ABCD'Matches.
    Fixed-length character string (4 bytes)'AB%[Figure]''ABCD'Does not match.
    'AB%%''ABCD'Matches.
    Example 2:
    The table below shows the comparison results when X'52454425 is specified for the pattern character string and the binary data is 52454452554d.
    Variable data typePattern character stringBinary dataComparison result
    BINARY typeX'52454425​'52454452554​dMatches.
    X'5245442525​'52454452554​dMatches.
Invalid pattern character string
  1. The table below shows the conditions that make a pattern character string invalid (KFPA11424-E message is issued):
    Related itemConditionExamples of invalid pattern character strings
    Normal primary*
    Normal primary+
    Normal primary?
    Normal primary preceding * , + , or ? is not specified.(*), (+), (?)
    regular-expression|regular-expressionA regular expression is not specified on both sides of the vertical bar (|).a|, (a|), (a||b)
    (regular-expression)No regular expression is specified within the parentheses.()
    Left and right parentheses do not match.(abc, abc)
    normal-primary{n}
    normal-primary{n,m}
    normal-primary{n,}
    The normal primary that should precede the repetition factor is not specified.{4}
    The repetition count specified for the repetition factor is invalid.a{-1}, a{4,2}
    Left and right curly brackets do not match.a{4, a4}
    [character-list...]
    [^character-list...]
    Character list contains a non-escaped special character. Note that a normal character set identifier can be specified.[a%c]
    The character specified before or after the minus sign (-) is invalid.[-], [c-a], [a--]
    No character list is specified within the square brackets.[], [^]
    Left and right square brackets do not match.[a-c, a-c]
    Escape characterThe last character of a pattern character string is an escape character.abc\ (when \ is specified as the escape character)
    Normal character set identifier specificationThe normal character set identifier is invalid.[:INVALID:]
Examples of pattern character strings
Table 2-6 shows typical examples of pattern character strings used in the SIMILAR predicate.

Table 2-6 Examples of pattern character strings used in the SIMILAR predicate

ItemPattern character stringMeaningExample
Pattern character stringPattern-matching character string
Front matchnnn%The leading portion of the character string is nnn.'ACT%'Character strings beginning with ACT, such as ACT, ACTOR, ACTION.
Rear match#1%nnnThe trailing portion of the character string is nnn.'%ING'Character strings ending with ING, such as ING, BEING, and HAVING.
Any match%nnn%Any portion of the character string contains nnn.N'%A%'Character strings containing A, such as A, ACT, CA, and TACT #2
Complete matchnnnCharacter string is the same as nnn.'EQUAL'EQUAL
Partial match_..._nnn_..._
i j
A specific portion (from the ith through the jth character) of the character string is the same as nnn, but the remaining characters are different.'_I_'Three-letter character strings in which the second letter is I, such as BIT, HIT, and KIT.
Repetition of at least oncemmm[0-9]+
1 i
or
mmm[:DIGIT:]+
1 i
The leading portion of the character string is mmm and the value beginning with the ith character is numeric.'KFPA11[0-9]+-E'
or
'KFPA11[:DIGIT :]+-E'
Character strings beginning with KFPA11 in which numerics begin at the seventh character, followed by -E, such as KFPA11104-E and KFPA11901-E.
Several selected charactersmmm(n|o)
1 i
or
mmm[no]
1 i
The leading portion of the character string is mmm and the ith character is n or o.'KFPA%-(W|E)'
or
'KFPA%-[WE]'
Character strings beginning with KFPA in which the last two characters are -E or -W, such as KFPA20008-W and KFPA11901-E.
Repetition of between zero and one timennno?mmmThe leading and trailing portions of the character string are nnn and mmm, respectively, and the character W may or may not be present between the two character strings.'OW?N'Character strings beginning with O and ending with N in which the character W may or may not be present between O and N, such as ON and OWN.
Repetition of at least zero timesnnno*mmmThe leading and trailing portions of the character string are nnn and mmm, respectively, and the character 0 is repeated at least 0 times between the character strings.10*1Character strings beginning with 1, followed by 0 at least zero times, and ending with 1, such as 11, 101, and 1001.
n repetitionsmmm{n}The leading portion of the character string is mmm, followed by n repetitions.[1-9]0{3}Character strings beginning with 1 through 9, followed by 0 repeated three times, such as 1000, 2000, and 3000.
Othernnn%mmmThe leading and trailing portions of the character string are nnn and mmm, respectively.'O%N'Character strings beginning with O and ending with N, such as ON, OWN, and ORIGIN.
%nnn%mmm%Character string containing nnn anywhere within it, and containing mmm anywhere in the following portion.'%O%N%'Character strings containing the character O and also containing the character N in the following portion, such as ON, ONE, DOWN, and COUNT.
nnn_..._
1 i
mmm%
j k
The first through the ith characters are nnn and the jth through kth characters are mmm.'CO_ _ECT%'Character strings beginning with CO and in which the fifth through seventh characters are ECT, such as CORRECT,
CONNECTOR,
and CONNECTION.
Note:
nnn and mmm are any character strings that do not contain a special character.
#1
Because the space character is also used as a comparison character, comparison with data containing trailing spaces produces the FALSE result.
#2
Special characters appropriate to individual national characters are used for the special characters in a national character string.
escape-character
Special characters within a pattern character string cannot be handled as regular characters. When a special character needs to be specified as a regular character, an escape character must also be specified. The escape character is any character that you specify following the ESCAPE keyword. You can then specify the defined escape character before the special character in the pattern character string, which causes the special character to be handled as a regular character. You can specify as the escape character a character literal, ? parameter, embedded variable, SQL variable name, or SQL parameter name.
Example 1
A character string containing '5%', such as '5%' or '25%':

 '%5\%%' ESCAPE '\'

Example 2
A character string ending with 'PRINT_REC', such as 'SQLPRINT_REC':

 '%PRINT\_REC' ESCAPE '\'

Example 3
A hexadecimal character string containing X'48695244425f' in the binary string, such as X'48695244425f':

 X'4869524442ee5f' ESCAPE X'ee'

The following characters can be specified as escape characters:
Item data typeSpecifiable character
Character data (CHAR, VARCHAR)Any one-byte character
Mixed character data (MCHAR, MVARCHAR)
National character data (NCHAR, NVARCHAR)Any two-byte character
Binary data (BINARY)Any single-byte value
Note
You must be sure to specify the special character following the escape character.
Notes
  1. For improved performance, the definition length of the column used in a value expression on the left-hand side of SIMILAR should be either a maximum of 255 bytes (CHAR, VARCHAR, MCHAR, MVARCHAR, or BINARY) or a maximum of 127 characters (NCHAR or NVARCHAR).
  2. Multi-byte characters stored in a CHAR or VARCHAR-type column are evaluated byte by byte. Consequently, if the character code for a single-byte character specified in a pattern character string is included in the character codes for the multi-byte characters, the result of the SIMILAR predicate is true.
    Example:
    Execute the following query in a condition where the query is set up in sjis character codes, the CHAR-type column C1 is in Table T1, and the row [Figure] is in column C1:
    SELECT C1 FROM T1 WHERE C1 LIKE '%A%' ;
    The character code for the character [Figure] in hexadecimal is 8341. The character code in hexadecimal for the character A in the pattern character string is 41. Therefore, because the character code for [Figure], which is a multi-byte character, includes the character code for the single-byte character A, the result of the SIMILAR predicate is true.
  3. If the pattern character string is extremely long or if the special characters {} are specified consecutively, search performance may deteriorate or the amount of memory used may increase.

(7) BETWEEN predicate

Format

row-value-constructor-1 [NOT] BETWEEN
  row-value-constructor-2 AND row-value-constructor-3

Conditions under which predicate is TRUE

The BETWEEN predicate is TRUE for those rows that satisfy the following condition:

row-value-constructor-2[Figure] row-value-constructor-1[Figure] row-value-constructor-3

If NOT is specified, the BETWEEN predicate is TRUE for those rows that do not satisfy the above condition.

Rules
(row-value-constructor-1)
  1. A row value constructor element consisting solely of a value specification cannot be specified.
  2. When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the BETWEEN predicate will be TRUE.
  3. ANY can be specified as a subscript for a repetition column. If ANY is specified and at least one element in the column meets specified conditions, the BETWEEN predicate will be TRUE. If the BETWEEN predicate is not TRUE and a condition specified for at least one element in the column is UNKNOWN, the BETWEEN predicate will be UNKNOWN. If the BETWEEN predicate is neither TRUE nor UNKNOWN, it will be FALSE.
  4. If a repetition column is specified with a subscript and the column contains no elements, the BETWEEN predicate will be UNKNOWN.
(row-value-constructor-2 and row-value-constructor-3)
  1. Repetition columns cannot be specified.
Common
  1. Row value constructor elements located in corresponding positions in row value constructors are treated as corresponding values. Corresponding values must have data types that are mutually convertible. If, however, national character data is specified in row-value-constructor-1, and character string literals are specified in the corresponding values for row-value-constructor-2 or row-value-constructor-3, the character string literals are treated as national character string literals. If a character string literal is treated as a national character string literal, HiRDB only checks the length of the character data, not the character code.

    [Figure]

  2. Values of any of the following data types cannot be specified in row-value-constructor-1, row-value-constructor-2, or row-value-constructor-3:
    • BLOB
    • BINARY with a minimum length of 32,001 bytes
    • BOOLEAN
    • Abstract data type

(8) Quantified predicate

Format

row-value-constructor= {= | <> | ^= | != | < | <=|> | >=}
   {{ANY | SOME} | ALL}
(table-subquery)
([SQL-optimization-specification-for-subquery-execution-method]
SELECT [{ALL | DISTINCT}] {selection expression |*}
(table-expression)
FROM table-reference [,table-reference]...
[WHERE search-condition]
[GROUP BY value-expression [,value-expression]...]
[HAVING search condition])

Conditions under which a predicate is TRUE

If either ANY or SOME is specified and if any one row in the results of a table subquery satisfies the comparison condition with respect to a row value constructor, the result of the quantified predicate is TRUE.

If ALL is specified and if all rows in the results of a table subquery satisfy the comparison condition with respect to a row value constructor, or if the result of a table subquery is the empty set, the result of the quantified predicate is TRUE.

Rules
  1. Values that yield any of the following data types as a result of an operation cannot be specified:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
  2. This predicate is indefinite with respect to rows in which the result of the row value constructor is null.
  3. For table subqueries, see 2.4 Subqueries.
  4. The SOME quantified predicate and the ANY quantified predicate produce the same results.
  5. Some quantified predicates have the same meaning as the IN predicate, as follows:
    Quantified predicateIN predicate
    row-value-constructor = row-value-constructor = ANY table-subquery
    or
    row-value-constructor = SOME table-subquery
    row-value-constructor IN table-subquery
    row-value-constructor <> ALL table-subqueryrow-value-constructor NOT IN table-subquery
  6. The following table shows the result of a quantified predicate in which either ANY or SOME is specified. If any row in the results of a table subquery satisfies specified conditions, the result of the quantified predicate is TRUE. If the results of comparison of all rows are all FALSE or if the result of a table subquery is the empty set, the result of the quantified predicate is FALSE. If neither is the case, the result of the quantified predicate is indefinite.

    Table 2-7 Result of a quantified predicate with ANY or SOME specified

    Result of comparing rows in subqueryResult of quantified predicate (ANY or SOME)
    TRUE rows foundTRUE
    No TRUE rowsIndefiniteIndefinite
    Not indefiniteFALSE
    Empty setFALSE
  7. Table 2-8 shows the results of a quantified predicate in which ALL is specified.
    The result of the quantified predicate is TRUE if the comparison results of all rows in the results of a table subquery are TRUE or if the results of a table subquery are TRUE. If neither of the above two conditions is applicable, the quantified predicate is indefinite.

    Table 2-8 Result of a quantified predicate with ALL specified

    Result of comparing rows in subqueryResult of quantified predicate (ALL)
    FALSE rows foundFALSE
    No FALSE rowsIndefiniteIndefinite
    Not indefiniteTRUE
    Empty setTRUE
  8. A qualified predicate cannot be specified in the following locations:
    • Search condition of an IF statement
    • Search condition of a WHILE statement
    • WHEN search condition (trigger action condition) of CREATE TRIGGER
  9. When specifying a repetition column in a row value constructor element, specify a subscript. If a repetition column is specified with a subscript and its elements meet specified conditions, the quantified predicate will be TRUE.
  10. ANY can be specified as a subscript for a repetition column. If ANY is specified and at least one element in the column meets specified conditions, the quantified predicate will be TRUE. If the quantified predicate is not TRUE and a condition specified for at least one element in the column is UNKNOWN, the quantified predicate will be UNKNOWN. If the quantified predicate is neither TRUE nor UNKNOWN, it will be FALSE.
  11. If a repetition column is specified with a subscript and the column contains no elements, the quantified predicate will be UNKNOWN.
  12. The number of columns in the row value constructor specified on the left side of a quantified predicate must have the same number of columns as the results of a table subquery.
Note
  1. Specifying a quantified predicate may cause HiRDB to create a work table. In this case, the processing of the quantified predicate may be subject to restrictions, depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 8 Installation and Design Guide.

(9) EXISTS predicate

Format

EXISTS
 (table-subquery)
    ([SQL-optimization-specification-for-subquery-execution-method]
      SELECT [{ALL|DISTINCT}] {selection-expression|*}
     (Table-Expression)
     FROM table-reference [,table-reference]...
        [WHERE search-condition]]...
        [GROUP BY value-expression [, value-expression]...]
        [HAVING search-condition])

Conditions under which a predicate is TRUE

The results of the EXISTS predicate are TRUE unless the results of the table subquery are the empty set.

Rules
  1. For table subqueries, see 2.4 Subqueries.
  2. The EXISTS predicate is used to determine whether the results of a table subquery are the empty set.
  3. The following table shows the results of an EXISTS predicate. The result of the EXISTS predicate is TRUE if the results of a table subquery are one or more rows. The EXISTS predicate is FALSE if the result of the subquery is the empty set.

    Table 2-9 Result of EXISTS predicate

    Number of rows meeting query condition as result of subqueryResult of EXISTS predicate
    One or more rowsTRUE
    0 rowsFALSE
  4. The EXISTS predicate cannot be specified in the following locations:
    • Search condition of an IF statement
    • Search condition of a WHILE statement
    • WHEN search condition (trigger action condition) of CREATE TRIGGER

(10) Boolean predicate

Format

value-expression IS [NOT] {TRUE|FALSE|UNKNOWN}

Conditions under which a predicate is TRUE

If the logical value of a value expression matches the specified TRUE, FALSE, or UNKNOWN, the Boolean predicate will be TRUE. If NOT is specified and the logical value of the value expression does not match the specified TRUE, FALSE, or UNKNOWN, the Boolean predicate will be TRUE.

Rules
  1. Table 2-10 shows the result of a predicate obtained by evaluating a Boolean predicate. If NOT is specified, the logical values shown in Table 2-10 are reversed.

    Table 2-10 Results of a predicate obtained by evaluating a Boolean predicate

    Logical value of value expressionIS TRUEIS FALSEIS UNKNOWN
    TRUETRUEFALSEFALSE
    FALSEFALSETRUEFALSE
    UNKNOWNFALSEFALSETRUE
  2. Values in the following data type can be specified in a value expression:
    • BOOLEAN
  3. An undefined Boolean value is the same as a null value.

(11) Structured repetition predicate

Format

ARRAY (column-specification [, column-specification]...)
  [ANY] (search-condition)

Conditions under which a predicate is TRUE

If the repetition column specified in ARRAY (column-specification [, column-specification]...) is treated as repetitions of multiple items that are a set of elements with the same subscript and if any of the elements meet specified search conditions, the structured repetition predicate will be TRUE.

Rules
ARRAY (column-specification [,column-specification]...)
  1. column-specification specifies a repetition column to be structured.
  2. The column specifications should be entirely from one index constituent column.
  3. The following cannot be specified in a column specification:
    [Figure]Columns of different tables
    [Figure]Columns derived from different tables
    "Different tables" includes tables with the same base table but different correlation names.
  4. Columns that make external references cannot be specified.
  5. The same column cannot be specified more than once.
  6. A maximum of 16 columns can be specified.
search-condition
Specifies a search condition. Structured repetition predicates are subject to the following rules.
  1. None of the following items can be specified in a search condition:
    [Figure]Subscripted column specifications
    [Figure]Columns other than a repetition column specified in ARRAY (column-specification [, column-specification]...)
    [Figure]Predicates containing a system-defined scalar function, a function call, or IS_USER_CONTAINED_IN_HDS_GROUP
    [Figure] Structured repetition columns
    [Figure]Predicates not containing a column specification
    [Figure]Subqueries
  2. If the NULL predicate is specified and the column does not contain any elements, the NULL predicate will be UNKNOWN. If a specified element is the NULL value, the NULL predicate will be TRUE.
Common rules
  1. Search conditions that contain a structured repetition predicate cannot be negated by NOT.
  2. Structured repetition predicates cannot be specified in a search condition in the IF or WHILE statement.
  3. Structured repetition predicates cannot be specified in the HAVING clause.
  4. Structured repetition predicates cannot be specified in a search condition in a CASE expression.
  5. An OR containing a structured repetition predicate and any of the following columns (excluding external reference columns) in its operand search condition cannot be specified:
    • Columns of different tables
    • Columns derived from different tables
    "Different tables" includes tables with the same base table but different correlation names.
  6. Structured repetition predicates cannot be specified in a search condition in a derived query expression in a view definition.
  7. When a structured repetition predicate in the ON search condition for a query specification including an outer-joined joined table is specified, specify columns of the inner table in the column specification.
  8. When a structured repetition predicate in the WHERE clause of a query specification including an outer-joined joined table is specified, specify columns of the outermost table in the outer join in the column specification.
Usage example

From a listing of students' grades, find the names of students who had a minimum score of 85 in mathematics. The grades list consists of a repetition column of 10 elements composed of subjects and scores.

SELECT name FROM grades-list
  WHERE ARRAY (subject,score) [ANY]
     (subject='mathematics' AND score>=85)

[Figure]

Note
The grades list has the following table definition and index definition:
Table definition:

CREATE TABLE grades-list (name MCHAR(10),
                         subject MCHAR(10) ARRAY[4],
                         score SMALLINT ARRAY[4];

Index definition:

CREAT INDEX subject-score
           ON grades-list (subject, score);