7.32.1 Specification format and rules for field references : Hitachi Advanced Database SQL Reference

(1) Specification format

field-reference ::= structure-value-expression.field-name
 
  structure-value-expression ::= value-expression

To Page Top

(2) Explanation of specification format

structure-value-expression:

Specify the column name of the structure data (STRUCT type) in the structure value expression. For details about value expressions, see 7.21 Value expression.

Note the following rules:

Specify structure data for the structure value expression.
You cannot specify a dynamic parameter for the structure value expression.

field-name:

Specify the name of the field to be referenced.

Field names that do not exist in the structure data cannot be specified.

The following is an example of specifying a field reference:

"DEPARTMENT"."DEPT_NAME"

DEPARTMENT is a STRUCT type column and DEPT_NAME is the name of a field in the DEPARTMENT column.

To Page Top

(3) Rules

If the structure value expression has a null value, the field reference will be a null value.
The data type corresponding to the specified field name becomes the data type of the result of the field reference.

The format for specifying field references and columns is, for example, "A.B" where both are specified by concatenating the identifiers with a period (.). HADB determines whether the specification is a field reference or a column specification according to the rules shown in the following table.

Note that A, B, C, and D in the table refer to the specification of identifiers. The numbers 1., 2., and 3. mean the priority level when HADB determines (1. has the highest priority).

Table 7‒52: How to determine field references and column specifications
Specification format	Specification assumed by HADB	Determining whether a field reference or a column specification
`A`	Column name	Column specification
`A`.`B`	1. `correlation-name`.`column-name` 1. `table-identifier`.`column-name` 1. `query-name`.`column-name`	Column specification
`A`.`B`	2. `column-name`.`field-name`	Field reference
`A`.`B`.`C`	1. `schema-name`.`table-identifier`.`column-name`	Column specification
	2. `correlation-name`.`column-name`.`field-name` 2. `table-identifier`.`column-name`.`field-name` 2. `query-name`.`column-name`.`field-name`	Field reference
	3. `column-name`.`field-name`.`field-name`	Field reference
`A`.`B`.`C`.`D`	1. `schema-name`.`table-identifier`.`column-name`.`field-name`	Field reference
	2. `correlation-name`.`column-name`.`field-name`.`field-name` 2. `table-identifier`.`column-name`.`field-name`.`field-name` 2. `query-name`.`column-name`.`field-name`.`field-name`
	3. `column-name`.`field-name`.`field-name`.`field-name`

Explanation:

Regarding the interpretation of identifiers by HADB, 1. indicates the highest priority.

Example:
```
CREATE FOREIGN TABLE "T1" ("T1" STRUCT {"T1" INTEGER})…
SELECT COUNT(*) FROM (SELECT "T1"."T1" FROM "T1")
```
The underlined part of the selection expression, "T1"."T1", could be either "table-identifier.column-name" or "column-name.field-name". In this case, it is determined as a "column-specification" of "table-identifier.column-name", according to the priority of the rules shown in the above table.
The above table explains up to the specification of "A.B.C.D", but "A.B.C.D.E" and thereafter are treated the same as "A.B.C.D".

To Page Top

(4) Examples

The following is an example of executing a SQL statement to search the employee table (EMPLIST).

The DEPARTMENT column is defined as a STRUCT type.
The MANAGER field of the DEPARTMENT column is defined as a STRUCT type.

CREATE FOREIGN TABLE "EMPLIST"("USER_ID" INTEGER,
                               "USER_NAME" VARCHAR(100),
                               "ADDRESS" VARCHAR(100),
                               "DEPARTMENT" STRUCT {"DEPT_NAME" VARCHAR(100),
                                                    "LOCATION" VARCHAR(100),
                                                    "MANAGER" STRUCT {
                                                               "MANAGER_ID" INTEGER,
                                                               "MANAGER_NAME" VARCHAR(100)
                                                                      }
                                                    }
                              )…

Example 1:

Search the employee name (USER_NAME) and department name (DEPT_NAME) from the employee table (EMPLIST).

SELECT "USER_NAME","DEPARTMENT"."DEPT_NAME" FROM "EMPLIST"

The underlined portion indicates the field reference.

Example 2:

Retrieves the employee name ( USER_NAME) and manager name ( MANAGER_NAME) from the employee table ( EMPLIST).

SELECT "USER_NAME","DEPARTMENT"."MANAGER"."MANAGER_NAME" FROM "EMPLIST"

The underlined portion indicates the field reference.

Example 3:

Add employee name (USER_NAME) and department name (DEPT_NAME) data to the department table (DEPTLIST).

INSERT INTO "DEPTLIST" ("USER_NAME","DEPT_NAME")
    SELECT "USER_NAME","DEPARTMENT"."DEPT_NAME" FROM "EMPLIST"

The underlined portion indicates the field reference.

Example 4:

Update the engineer flag (E_FLG) in the member table (MEMBERLIST) using data from the employee table (EMPLIST).

UPDATE "MEMBERLIST" SET "E_FLG" = 'Y' WHERE EXISTS 
     (SELECT * FROM "EMPLIST" 
          WHERE "EMPLIST"."DEPARTMENT"."DEPT_NAME" IN ('ENGINEERING','TECHNICAL')
            AND "EMPLIST"."USER_ID" = "MEMBERLIST"."USER_ID"
      )

The underlined portion indicates the field reference.

To Page Top