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
(12) XMLEXISTS 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 defined 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.
  10. A leap second is treated as a value smaller than one minute.
    Example:
    '00:00:61' < '00:01:00'

(2) NULL predicate

Format

value-expression IS [NOT] NULL

Conditions under which a predicate is TRUE

The NULL predicate is TRUE for rows in which the value of the specified value expression is a null value. If NOT is specified, it is TRUE for rows in which the value of the specified value expression is not a null value. For details about the NULL value, see 1.7 Null value.

Rules
  1. Value expressions of the following data types cannot be specified:
    • BLOB (except for the ? parameter or when specified with only one embedded variable)
    • BINARY with a minimum defined length of 32,001 bytes (except for the ? parameter or when specified with only one embedded variable)
    • BOOLEAN
  2. 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.
  3. If a subscripted repetition column is specified and a specified element is NULL, the NULL predicate will be TRUE.
  4. If a subscripted repetition column is specified and the column does not contain any elements, the NULL predicate will be UNKNOWN.
  5. 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.
Note
  • The NULL predicate is TRUE if overflow error suppression is set or if there is a null value due to an overflow of the operation result of the value expression.

(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 defined length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
  2. A maximum of 30,000 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 9 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.

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 defined 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 typesPattern character string or escape character string data type
    Character string dataNational character string dataMixed character string dataBinary data
    Default character setEBCDIKUTF16
    Character string dataDefault character setYNNNYN
    EBCDIKR#3YNN
    UTF16R#3NY
    National character string dataR#1NNYNN
    Mixed character string dataYNNNYN
    Binary dataR#2NNNNY
    Legend:
    Y: Can be specified.
    R: Can be specified with restrictions.
    N: Cannot be specified.
    #1
    Only a character string literal can be specified in a pattern character string. In this case, the character string literal is treated as a national character string literal. When a character string literal is treated as a national character string literal, the length of the character string data is checked, but the character encoding is not checked. When specifying a special character, make sure to specify it using a double-byte character.
    #2
    Only a hexadecimal character string literal can be specified.
    #3
    Only a character string literal, embedded variable, or ? parameter can be specified.

  4. Use the same character set for the value expression, pattern character string, and escape character string. If the pattern character string and escape character string are one of those listed below, however, they are converted to the character set of the value expression, enabling comparison:
    [Figure]Character string literal
    [Figure]Embedded variable (default character set)
    [Figure]? parameter
  5. The special characters that can be included in the pattern character string include the underscore (_), percent sign (%), and escape character. Specify the code for the special characters as binary data. The following table describes how the special characters underscore and percent sign are interpreted in the pattern character string.

    Table 2-3 Meaning of special characters in the pattern character string (LIKE predicate)

    Special characterItem specification data typeMeaning of the special characterMethod of specifying the special character
    Underscore (_)Character string dataDefault character set#1Any single characterSingle-byte underscore (_)
    EBCDIK#1
    UTF16
    National character string dataDouble-byte underscore ([Figure])
    Mixed character string dataSingle-byte underscore (_)
    Binary dataAny single byte5f (hex code for an underscore)#3
    Percent sign (%)Character string dataDefault character set#1A character string of any length (one or more characters)Single-byte percent sign (%)
    EBCDIK#1
    UTF16
    National character string data#2Double-byte percent sign ([Figure])
    Mixed character string dataSingle-byte percent sign (%)
    Binary dataA byte string of any length (one or more bytes)25 (hex code for percent sign)#3
    #1
    All characters or data values are treated as single-byte characters.
    #2
    All characters or data values are treated as double-byte characters.
    #3
    When specifying special characters in binary data, specify the code indicating the special character (_ or %) using the character encoding specified when HiRDB was set up.

  6. 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.
  7. 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.
  8. 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, we recommend that the percent sign 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

The following table provides examples of typical pattern character strings used in the LIKE predicate.

Table 2-4 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 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%The character string contains nnn at any position.N'%OR%'Character strings containing "or", such as OR, More, and CoLoR.#2
Complete matchnnnThe character string is equal to nnn.'EQUAL'EQUAL
Partial match_..._nnn_..._A specific portion 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_..._
mmm%
The leading portion of the character string is nnn and the trailing portion 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 underscore 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:
Data type of itemCharacter that can be specified
Character string data (CHAR, VARCHAR)Default character setAny single character (single-byte character)#1
EBCDIK
UTF16Any single character#2
Mixed character string data (MCHAR, MVARCHAR)Any single character#2
National character data (NCHAR, NVARCHAR)Any single character (double-byte character)#3
Binary data (BINARY)Any single byte value
Note
Care must be taken that the special character is specified following the escape character.
#1
All characters or data values are treated as single-byte characters.
#2
Data values that are not characters are treated as characters with the smallest length allowed for the character encoding (two bytes for UTF-16 and one byte for UTF-8 and SJIS).
#3
All characters or data values are treated as double-byte characters.
Notes
  1. For improved performance, the defined 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.

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:
    Data type of value expressionData type of pattern character string or escape character
    Character string dataNational character string
    data
    Mixed character string
    data
    Default
    character set
    EBCDIKUTF16
    Character string dataDefault character setYNNNY
    EBCDIKR#2YNN
    UTF16R#2NY
    National character string dataR#1NNYN
    Mixed character string dataYNNNY
    Legend:
    Y: Can be specified.
    R: Can be specified with restrictions.
    N: Cannot be specified.
    #1
    Only a character string literal can be specified in a pattern character string. In this case, the character string literal is treated as a national character string literal. When a character string literal is treated as a national character string literal, the length of the character string data is checked, but the character encoding is not checked. When specifying a special character, be sure to specify it using a double-byte character.
    #2
    This can specify only a character string literal, embedded variable, or ? parameter.

  4. Use the same character set for the value expression, pattern character string, and escape character string. If the pattern character string and escape character string are one of those listed below, however, they are converted to the character set of the value expression, enabling comparison:
    [Figure]Character string literal
    [Figure]Embedded variable (default character set)
    [Figure]? parameter
  5. The following special characters can be used in a pattern character string: the underscore, the percent sign, and escape characters. The following table describes how the special characters underscore and percent sign are interpreted in the pattern character string.

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

    Special characterData type of item specificationMeaning of the special characterMethod of specifying the special character
    Underscore (_)Character string dataDefault character set#1Any single characterSingle-byte underscore (_)
    EBCDIK#1
    UTF16
    National character string dataDouble-byte underscore ([Figure])
    Mixed character string dataSingle-byte underscore (_)
    Percent sign (%)Character string dataDefault character set#1A character string of any length (one or more characters)Single-byte percent sign (%)
    EBCDIK#1
    UTF16
    National character string data#2Double-byte percent sign ([Figure])
    Mixed character string dataSingle-byte percent sign (%)
    #1
    All characters or data values are treated as single-byte characters.
    #2
    All characters or data values are treated as double-byte characters.

  6. Comparison of the following characters with pattern character string data is not case-sensitive:
    Alphanumeric national characters and mixed characters
  7. 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.
  8. 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.
  9. 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

The following table provides examples of typical pattern character strings used in the XLIKE predicate.

Table 2-6 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 mAx#4
Partial match_..._nnn_..._A specific portion 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_..._
mmm%
The leading portion of the character string is nnn and the trailing portion 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 _) must 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 underscore 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:
Data type of itemCharacter that can be specified
Character string data (CHAR, VARCHAR)Default character setAny single character (single-byte character)#1
EBCDIK
UTF16Any single character#2
Mixed character string data (MCHAR, MVARCHAR)Any single character#2
National character data (NCHAR, NVARCHAR)Any single character (double-byte character)#3
Note
Care must be taken that the special character is specified following the escape character.
#1
All characters or data values are treated as single-byte characters.
#2
Data values that are not characters are treated as characters with the smallest length allowed for the character encoding (two bytes for UTF-16 and one byte for UTF-8 and SJIS).
#3
All characters or data values are treated as double-byte characters.
Notes
  1. For improved performance, the defined 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 defined 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:
    Data type of value expressionData type of pattern character string or escape character
    Character string dataNational character string dataMixed character string dataBinary data
    Default character setEBCDIKUTF16
    Character string dataDefault character setYNNNYN
    EBCDIKR#3YNN
    UTF16R#3NY
    National character string dataR#1NNYNN
    Mixed character string dataYNNNYN
    Binary dataR#2NNNNY
    Legend:
    Y: Can be specified.
    R: Can be specified with restrictions.
    N: Cannot be specified.
    #1
    Only a character string literal can be specified in a pattern character string. In this case, the character string literal is treated as a national character string literal. When a character string literal is treated as a national character string literal, the length of the character string data is checked, but the character encoding is not checked. When specifying a special character, make sure to specify it using a double-byte character.
    #2
    This can specify only a hexadecimal character string literal.
    #3
    This can specify only a character string literal, embedded variable, or ? parameter.

  4. Use the same character set for the value expression, pattern character string, and escape character string. If the pattern character string and escape character string are one of those listed below, however, they are converted to the character set of the value expression, enabling comparison:
    [Figure]Character string literal
    [Figure]Embedded variable (default character set)
    [Figure]? parameter
  5. 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 : ]

  6. 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 representation in binary data
    No character set specifiedCharacter set specified
    EBCDIKUTF16
    _ (underscore)X'5F'X' 6D'U+005F
    % (percent sign)X' 25'X' 6C'U+0025
    * (asterisk)X' 2A'X' 5C'U+002A
    + (plus sign)X' 2B'X' 4EU+002B
    ? (question mark)X' 3F'X' 6F'U+003F
    | (vertical line)X' 7C'X' 4F'U+007C
    ( (left parenthesis)X' 28'X' 4D'U+0028
    ) (right parenthesis)X' 29'X' 5D'U+0029
    { (left curly bracket)X' 7B'X' C0'U+007B
    } (right curly bracket)X' 7D'X' D0'U+007D
    [ (left square bracket)X' 5B'X' 4A'U+005B
    ] (right square bracket)X' 5D'X' 5A'U+005D
    Escape characterValue specified in ESCAPE
    - (minus sign)#X' 2D'X' 60'U+002D
    : (colon)#X' 3A'X' 7A'U+003A
    ^ (circumflex)#X' 5E'X' 5F'U+005E
    #: 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.
  7. The following table explains the meaning of each regular expression specification used in a pattern character string.

    Table 2-7 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 listed in the table below (what is meant by [:WHITESPACE:] depends on the type of character codes)
    The following table lists the character codes of the characters included in [:WHITESPACE:]:
    Unicode(UTF-8)Shift JIS kanjiEUC Japanese kanji,
    EUC Chinese kanji
    Chinese kanji (GB18030)LANG-CCharacter name in the Unicode standard character set
    Default character setUTF16Default character setEBCDIK
    X'09'U+0009X'09'X'05'X'09'X'09'X'09'Horizontal Tabulation
    X'0A'U+000AX'0A'X'15'X'0A'X'0A'X'0A'Line Feed
    X'0BU+000BX'0BX'0B'X'0BX'0BX'0BVertical Tabulation
    X'0C'U+000CX'0C'X'0C'X'0C'X'0C'X'0C'Form Feed
    X'0D'U+000DX'0D'X'0D'X'0D'X'0D'X'0D'Carriage Return
    X'20'U+0020X'20'X'40'X'20'X'20'X'20'Space
    X'C285'U+0085------X'81308135​'--Next Line#
    X'C2A0'U+00A0------X'81308432​'--No-Break Space#
    X'E19A80'U+1680------X'8134AC34'--Ogham Space Mark#
    X'E28080'U+2000------X'8136A336'--En Quad#
    X'E28081'U+2001------X'8136A337'--Em Quad#
    X'E28082'U+2002------X'8136A338'--En Space#
    X'E28083'U+2003------X'8136A339'--Em Space#
    X'E28084'U+2004------X'8136A430'--Three-Per-Em Space#
    X'E28085'U+2005------X'8136A431'--Four-Per-Em Space#
    X'E28086'U+2006------X'8136A432'--Six-Per-Em Space#
    X'E28087'U+2007------X'8136A433'--Figure Space#
    X'E28088'U+2008------X'8136A434'--Punctuation Space#
    X'E28089'U+2009------X'8136A435'--Thin Space#
    X'E2808A'U+200A------X'8136A436'--Hair Space#
    X'E280A8'U+2028------X'8136A635'--Line Separator#
    X'E280A9'U+2029------X'8136A636'--Paragraph Separator#
    X'E280AF'U+202F------X'8136A732'--Narrow No-Break Space#
    X'E38080'U+30000x8140--X'A1A1'X'A1A1'--Ideographic Space#
    Legend:
    --: Not applicable
    #
    Not included in [:WHITESPACE:] when the value expression is character string type.

  8. Binary data cannot be specified for a normal character set identifier.
  9. 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 following table lists 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 following table lists 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 following table describes 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
The following table provides examples of typical pattern character strings used in the SIMILAR predicate.

Table 2-8 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_..._A specific portion 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]+
or
mmm[:DIGIT:]+
The leading portion of the character string is mmm and the trailing portion is a numerical value.'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)
or
mmm[no]
The leading portion of the character string is mmm and the ith character is n or o (i is any number).'KFPA%-(W|E)'
or
'KFPA%-[WE]'
Character strings beginning with KFPA in which the last two characters are -W or -E, 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_..._
mmm%
The leading portion of the character string is nnn and the trailing portion is 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:
Data type of itemCharacter that can be specified
Character string data (CHAR, VARCHAR)Default character setAny single character (single-byte character)#1
EBCDIK
UTF16Any single character#2
Mixed character string data (MCHAR, MVARCHAR)Any single character#2
National character data (NCHAR, NVARCHAR)Any single character (double-byte character)#3
Binary data (BINARY)Any single byte value
Note
You must be sure to specify the special character following the escape character.
#1
All characters or data values are treated as single-byte characters.
#2
Data values that are not characters are treated as characters with the smallest length allowed for the character encoding (two bytes for UTF-16 and one byte for UTF-8 and SJIS).
#3
All characters or data values are treated as double-byte characters.
Notes
  1. For improved performance, the defined 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 defined 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 lists the results 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-9 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. The table below lists the results of a quantified predicate with ALL 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-10 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 9 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 lists 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-11 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. The following table lists the results 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-12 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 must 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]XMLEXISTS predicate
    [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);

(12) XMLEXISTS predicate

A predicate that can be used with HiRDB XML Extension.

For details, see 1.14.4(1) XMLEXISTS predicate.