7.31.1 Specification format and rules for array element references

(1) Specification format

array-element-reference::= array-value-expression[array-index]
 
  array-value-expression::= value-expression-primary
  array-index::= {unsigned-integer-literal|ANY[(identification-number)]}
  identification-number::= unsigned-integer-literal

To Page Top

(2) Explanation of specification format

array-value-expression:

Specifies the array data in the for of a value-expression-primary for the array value expression. For details about the value expression primary, see 7.21 Value expression.

Note the following rules:

• Specify array data for the array value expression.

• You cannot specify a dynamic for the array value expression.

array-index:

Specify the array index in either of the following formats:

• Unsigned integer literal

• ANY[(identification-number)]

unsigned-integer-literal

Specifies the element number of the array data specified in the array value expression.

(Example)

"C1"[2]

The underlined portion is an array index, specifying an unsigned integer literal. In this case, it refers to the array element whose element number is 2 in the C1 column (array-type column). For an example of when a signed integer literal is specified as an array index, see (4) Examples.

Note the following rules:

• Specify an unsigned integer literal in the range from 1 to 30,000 for the array index.

• The array index cannot be a value greater than the maximum number of array data elements specified in the array value expression.

ANY[(identification-number)]:

Specifies an array element reference with ANY when referring to any array element in the array data specified in the array value expression.

Note the following rules:

• An array element reference with ANY may only be specified in the predicate of a WHERE clause.

• Only one table can be evaluated by a conditional expression specifying an array element reference with ANY.

identification-number:

If you wish to consider multiple array element references as a single pair for predicate evaluation, specify the same identification number for each array element reference. For an example of specifying an identification number, see (4) Examples.

Note the following rules:

• Specify an unsigned integer literal in the range from 1 to 255 for the identification number.

• For array element references that omit the identification number, HADB assigns an identification number that does not overlap with the identification numbers of other array element references specified in the WHERE clause.

  (Example)

  If "C1"[ANY]=1 AND "C2"[ANY]=2 is specified, it becomes "C1"[ANY(1)]=1 AND "C2"[ANY(2)]=2.

  If "C1"[ANY]="C2"[ANY] is specified, it becomes "C1"[ANY(1)]="C2"[ANY(2)].

To Page Top

(3) Rules

■Common rules

1. If the array value expression has a null value, the array element reference will be a null value.

2. If the array value expression is empty array data, the array element reference will be a null value.

3. The element data type of the result of the array value expression becomes the data type of the result of the array element reference.

4. An array element reference cannot be specified in a value expression of a row value constructor element of a row value constructor.

■Rules for when an unsigned integer literal is specified in an array index

1. The result of an array element reference is the value of the array element in the array data specified in the array value expression that has the same element number as the value specified in the array index.

2. If a value greater than the number of array elements in the array data specified in the array value expression is specified as an array index, the array element reference will be a null value.

■Rules when ANY is specified as an array index (including the case of ANY (identification number))

1. An array element reference with ANY can be specified in the following places among the predicates specified in the WHERE clause.

   • NULL predicate value expression

   • Value expression to the left of the IN predicate

     However, if a table subquery is specified to the right of the IN predicate, the value expression to the left of the IN predicate cannot contain an array element reference with ANY.

   • Value expression 1 to the left of the BETWEEN predicate

   • Value expressions on the left or right side of the comparison predicate

   • LIKE predicate match value

   • LIKE_REGEX predicate match value

2. An array element reference with ANY cannot be specified in the search conditions of a CASE expression.

3. An array element reference with ANY cannot be specified in the following value expressions.

   • Value expression specified for THEN in a CASE expression

   • Value expression specified for ELSE in a CASE expression

   • Value expression specified as the return value or predefined return value of the scalar function DECODE

   • Value expression specified as the return value or predefined return value of the scalar function LTDECODE

   • Structure value expression for field reference

   • Start value or end value of the ADB_GENERATE_SERIES function

4. Whether or not the identification number is the same is determined for each WHERE clause.

   (Example)

   SELECT "T1"."C2" FROM "T1" WHERE "T1"."C1"[ANY(1)] = 1
   UNION ALL
   SELECT "T2"."C2" FROM "T2" WHERE "T2"."C1"[ANY(1)] = 2

   In the SELECT statement above, two identification numbers 1 are specified, but whether the identification numbers are the same is determined for each WHERE clause. Therefore, in the above case, the identification numbers are not considered to be the same (the SELECT statement is not an error).

5. A column from a different table cannot be specified in a predicate that contains an array element reference with ANY.

   Example of an SQL statement that generates an error:

   SELECT * FROM "T1","T2" WHERE "T1"."C1"[ANY(1)] = "T2"."C1"

   SELECT * FROM "T1","T2" WHERE "T1"."C1"[ANY] = "T2"."C1"

   The SELECT statement above specifies an array element reference with ANY as the underlined predicate. Because columns from different tables (table T1 and table T2) are specified, the above SELECT statement results in an error.

6. Columns from different tables cannot be specified in the column specification of an array element reference with the same identification number.

   Example of an SQL statement that generates an error:

   SELECT * FROM "T1","T2"
     WHERE "T1"."C1"[ANY(1)] = 1 AND "T2"."C1"[ANY(1)] = 2

   The underlined portion indicates the column specification of the array element reference. Although the same value 1 is specified for both identification numbers, the SELECT statement above will result in an error because the column specification specifies columns from different tables (Table T1 and Table T2).

7. A scalar subquery cannot be specified for a predicate that includes an array element reference with ANY.

   Example of an SQL statement that generates an error:

   SELECT "T1"."C1","T1"."C2" FROM "T1"
     WHERE "T1"."C1"[ANY] = (SELECT "T2"."C1" FROM "T2" WHERE "T2"."C2" = 10)

   SELECT "T1"."C1","T1"."C2" FROM "T1"
     WHERE "T1"."C1"[ANY(1)] = (SELECT "T2"."C1" FROM "T2" WHERE "T2"."C2" = "T1"."C2")

   The underlined parts are predicates containing array element references specifying ANY. The above SELECT statement is an error because a scalar subquery is specified in this predicate.

   Note that if you wish to specify a scalar subquery for a predicate containing an array element reference that specifies ANY, consider rewriting the SQL statement to specify a collection derived table. The following is an example of the above example rewritten as a SELECT statement specifying a collection derived table.

   (Example) Rewriting example

   SELECT "T1"."C1","T1"."C2" FROM "T1",UNNEST("T1"."C1") "DT"
     WHERE "DT"."C1" = (SELECT "T2"."C1" FROM "T2" WHERE "T2"."C2" = 10)

   SELECT "T1"."C1","T1"."C2" FROM "T1",UNNEST("T1"."C1") "DT"
     WHERE "DT"."C1" = (SELECT "T2"."C1" FROM "T2" WHERE "T2"."C2" = "T1"."C2")

   The underlined portion indicates the collection derived table.

8. The maximum number of entities of identification numbers of array element references that can be specified in one SQL statement is 255.

   Moreover, if a viewed table is specified in an SQL statement, equivalent exchange is performed to convert the viewed table into a derived table, and then the number of entities of identification numbers is checked.

9. The identification numbers of the array element references specified in the WHERE clause of the query specification are automatically renumbered by HADB so that each is sequentially numbered from 1.

10. If an internal derived table that contains array element references with ANY specified during the derived query is specified in the SQL statement and the internal derived table is expanded, the identification number of the array element reference in the derived query of the internal derived table is automatically renumbered by HADB so that it does not overlap with the identification number of the array element reference specified in the query specification that specified the internal derived table.

11. The result of a search condition involving an array element reference with ANY is obtained as follows.

    ■When multiple predicates specify array element references with the same identification number

    Results are obtained for search conditions including multiple predicates that specify array element references with the same identification number. The result of a search condition is obtained as follows.

    • When determining the result of a search condition, multiple array element references with the same identification number included in the search condition are considered as a pair for each array element with the same array index. Then, evaluation is performed on all array indexes (for the highest number of array elements in the array value expression of multiple array element references), respectively. If the array indexes exceed the number of array elements in the array element reference array value expression, the null value is complemented with the value corresponding to the exceeded array index and evaluation is performed. The results of a search condition are determined according to the contents of the following table.

      No.	Condition	Search condition result
      1	If there exists at least one pair for which the result of the search condition is True	True
      2	When the condition of item 1 is not satisfied and there exists at least one pair for which the result of the search condition is undefined	Undefined*
      3	If the conditions of items 1 and 2 are not met	False

      Note* For the NULL predicate, the condition in item 2 is excluded. Therefore, if the condition in item 1 is not satisfied, the result of the search condition will be False.

      The result of a search condition involving an array element reference with ANY is the same as the result of a search condition that connects a search condition where each pair of array elements is evaluated with a OR condition. This is illustrated in the following example:

      (Example)

      The C1 column is assumed to be an array-type column with 2 array elements. The C2 column is assumed to be an array-type column with 3 array elements.

      Search condition containing multiple predicates specifying array element references with the same identification number	Search conditions that produce the same results as those specified to the left
      "C1"[ANY(1)] = 1 AND "C2"[ANY(1)] = 2	("C1"[1] = 1 AND "C2"[1] = 2) OR ("C1"[2] = 1 AND "C2"[2] = 2) OR ("C1"[3] = 1 AND "C2"[3] = 2) For "C1"[3] whose array indexes exceed the number of array elements in the array value expression, the null value is complemented and evaluated.

    ■When multiple predicates do not specify array element references with the same identification number

    A result is obtained for each predicate. The result of the predicate is obtained as follows.

    • When determining the result of a predicate, an array element reference specified in only one place in the predicate is evaluated for each array element. Predicate results are determined according to the contents of the following table.

      No.	Condition	Predicate result
      1	If there is at least one array element for which the result of the predicate is True	True
      2	If the condition of item 1 is not satisfied and there is at least one array element for which the result of the predicate is undefined	Undefined*
      3	If the conditions of items 1 and 2 are not met	False

      Note* For the NULL predicate, the condition in item 2 is excluded. Thus, if the condition in item 1 is not satisfied, the result of the predicate will be False.

      The result of a predicate containing an array element reference with ANY is the same as the result of a search condition that connects predicates that are evaluated for each array element with the OR condition. This is illustrated in the following example:

      (Example)

      The C1 column is assumed to be an array-type column with 3 array elements.

      Predicate containing an array element reference specifying ANY	Search conditions that produce the same results as those specified to the left
      "C1"[ANY] = 1	"C1"[1] = 1 OR "C1"[2] = 1 OR "C1"[3] = 1

    • When determining the result of a predicate, multiple array element references specifying the same identification number are considered one pair for each array element with the same array index. Then, evaluation is performed on all array indexes (for the highest number of array elements in the array value expression of multiple array element references), respectively. If the array indexes exceed the number of array elements in the array element reference array value expression, evaluation is performed by complementing the null value with the value corresponding to the exceeded array index. Predicate results are determined according to the contents of the following table.

      No.	Condition	Predicate result
      1	If there is at least one pair for which the result of the predicate is True	True
      2	If the condition of item 1 is not satisfied and there exists at least one pair for which the result of the predicate is undefined	Undefined*
      3	If the conditions of items 1 and 2 are not met	False

      Note* For the NULL predicate, the condition in item 2 is excluded. Thus, if the condition in item 1 is not satisfied, the result of the predicate will be False.

      The result of a predicate containing an array element reference with ANY is the same as the result of a search condition that connects predicates that are evaluated for each pair of array elements with the OR condition. This is illustrated in the following example:

      (Example)

      The C1 column is assumed to be an array-type column with 2 array elements. The C2 column is assumed to be an array-type column with 3 array elements.

      Predicate containing an array element reference specifying ANY	Search conditions that produce the same results as those specified to the left
      "C1"[ANY(1)] = "C2"[ANY(1)]	"C1"[1] = "C2"[1] OR "C1"[2] = "C2"[2] OR "C1"[3] = "C2"[3] For "C1"[3] whose array indexes exceed the number of array elements in the array value expression, the null value is complemented and evaluated.

    • When determining the result of a predicate, multiple array element references specifying different identification numbers are considered as one pair per array element of an array index, and evaluation is performed on all combinations of array indexes, respectively. Predicate results are determined according to the contents of the following table.

      No.	Condition	Predicate result
      1	If there is at least one pair for which the result of the predicate is True	True
      2	If the condition of item 1 is not satisfied and there exists at least one pair for which the result of the predicate is undefined	Undefined*
      3	If the conditions of items 1 and 2 are not met	False

      Note* For the NULL predicate, the condition in item 2 is excluded. Thus, if the condition in item 1 is not satisfied, the result of the predicate will be False.

      The result of a predicate containing an array element reference with ANY is the same as the result of a search condition that connects predicates that are evaluated for each pair of array elements with the OR condition. This is illustrated in the following example:

      (Example)

      The C1 column is assumed to be an array-type column with 2 array elements. The C2 column is assumed to be an array-type column with 3 array elements.

      Predicate containing an array element reference specifying ANY	Search conditions that produce the same results as those specified to the left
      "C1"[ANY(1)] = "C2"[ANY(2)]	"C1"[1] = "C2"[1] OR "C1"[1] = "C2"[2] OR "C1"[1] = "C2"[3] OR "C1"[2] = "C2"[1] OR "C1"[2] = "C2"[2] OR "C1"[2] = "C2"[3]

To Page Top

(4) Examples

■Example of specifying an unsigned integer literal as an array index

The following is an example of executing an SQL statement to search for table T1.

The C2 column is an array-type column, where the values in {} indicate the value of each array element.

The element data type of the C2 column will be INTEGER.

Example 1

Determine the value of the second array element in column C2 of table T1.

SELECT "C2"[2] AS "AER1" FROM "T1"

The underlined portion indicates the array element reference. To determine the value of the second array element, specify 2 as an array index.

Execution result example

Because the element data type of the C2 column is the INTEGER type, the data type of the execution result is the INTEGER type.

Example 2

Determine the value in column C1 of the row where the value of the second array element in column C2 of Table T1 is 1.

SELECT "C1" FROM "T1" WHERE "C2"[2]=1

The underlined portion indicates the array element reference. To determine the value of the second array element, an array index of 2 is specified.

Execution result example

■Example of specifying ANY as an array index

The following is an example of executing an SQL statement to search the REPORTtable shown below.

The SUBJECT and SCORE columns are array-type columns, where the value in { } indicates the value of each array element.

Example 1

Determine the name of the person taking the physics exam.

SELECT "NAME" FROM "REPORT" 
    WHERE "SUBJECT"[ANY] = 'physics'

The underlined portion indicates the array element reference. The array index ANY is specified to search for whether the value of the array element in the SUBJECT column is physics.

Execution result example

Example 2

Determine the names of those who scored 80 or more in mathematics and 80 or more in physics.

SELECT "NAME" FROM "REPORT" 
    WHERE "SUBJECT"[ANY(1)] = 'math' AND "SCORE"[ANY(1)] >= 80          ...1
      AND "SUBJECT"[ANY(2)] = 'physics' AND "SCORE"[ANY(2)] >= 80       ...2

The underlined portion indicates the array element reference.

1. The requirement is specified that the math score must be at least 80 points. The condition is specified that the value of the array element in the SUBJECT column has math (math) and the value of the array element in the SCORE column showing math scores is 80 or higher.

2. The requirement is specified that the physics score must be at least 80 points. The condition is specified that the value of the array element in the SUBJECT column has physics (physics) and the value of the array element in the SCORE column indicating the score of physics is 80 or more.

Important

Array element references that specify the same identification number are considered as one pair and the predicate is evaluated. The array element reference in 1. above specifies 1 for the identification number, so it is considered a single pair and the predicate is evaluated. The array element reference in 2. above specifies 2 for the identification number, so it is considered a single pair and the predicate is evaluated.

Execution result example

To Page Top