Hitachi

Hitachi Advanced Database SQL Reference

6.8.3 Effective scope of scope variables

The following table shows examples of the effective scope of scope variables.

Table 6‒16: Examples of the effective scope of scope variables

No.

SQL example

Scope variable

A.T1

X

A.T2

A.T3

Y

A.T4

A.T5

Z

1

SELECT "X"."C1","T2"."C2"

N

Y

Y

N

Y

N

N

N

2

FROM "A"."T1" "X",

N

Y

Y

N

Y

N

N

N

3

"A"."T2",

N

Y

Y

N

Y

N

N

N

4

(SELECT * FROM "T3"

N

N

N

Y

N

N

N

N

5

WHERE "T3"."C1"=100) "Y"

N

N

N

Y

N

N

N

N

6

WHERE "X"."C1"=100 AND

N

Y

Y

N

Y

N

N

N

7

"X"."C1"=ANY(

N

Y

Y

N

Y

N

N

N

8

SELECT "T4"."C1" FROM "T4",

N

N

N

N

N

Y

N

Y

9

(SELECT *

N

N

N

N

N

N

Y

N

10

FROM "T5"

N

N

N

N

N

N

Y

N

11

WHERE "T5"."C1"="X"."C1")"Z"

N

Y

Y

N

Y

N

Y

N

12

WHERE "T4"."C2"="A"."T2"."C2")

N

Y

Y

N

Y

Y

N

Y
Legend:

Y: Has scope.

N: Does not have scope.

A: Schema name

T1~T5: Table identifier

X,Y,Z: Correlation name

C1, C2: Column name

This section describes the effective scope of scope variables in SELECT, UPDATE, and DELETE statements.

Organization of this subsection

(1) The effective scope of scope variables specified in the FROM clause of a SELECT statement

The effective scope of the scope variable encompasses the query specification that actually contains the scope variable identifier in its FROM clause, as well as any search conditions in its subqueries. However, the effective scope does not extend to derived tables specified in the FROM clause that actually contains the scope variable identifier. The following figure shows an example.

Figure 6‒12: Example of the effective scope of scope variable T1 specified in a FROM clause (1 of 2)

[Figure]

Explanation

  1. The scope variable T1 can be referenced in the query specification that actually contains the scope variable in its FROM clause.

  2. The scope variable T1 cannot be referenced in the derived table specified in the same FROM clause as the FROM clause that actually contains the scope variable identifier.

  3. The scope variable T1 can be referenced in the query specification that actually contains the scope variable in its FROM clause.

  4. The scope variable T1 cannot be referenced outside of the search conditions of the subquery.

  5. The scope variable T1 can be referenced in the search conditions of the subquery contained in the query that immediately contains the scope variable in its FROM clause.

  6. The scope variable T1 cannot be referenced outside of the query.

Figure 6‒13: Example of the effective scope of scope variable T1 specified in a FROM clause (2 of 2)

[Figure]

Explanation

These are examples of specifying joined tables.

  1. The scope variable T1 can be referenced in the query that immediately contains the scope variable in its FROM clause.

  2. The scope variable T1 can be referenced because T1 is specified as a table reference in the specification of the joined table.

  3. The scope variable T1 cannot be referenced because T1 is not specified as a table reference in the specification of the joined table.

  4. The scope variable T1 can be referenced because T1 is specified as a table reference in the specification of the joined table.

Figure 6‒14: Example of the effective scope of a scope variable (if a query name is specified)

[Figure]

[Explanation]

A query name cannot be qualified with a schema name. If qualified with a schema name, the query name is treated as a table identifier rather than a query name. If there is a table identifier that has the same name as a query name, the table identifier is treated as a query name in the effective scope of the query name. However, outside the effective scope of the query name, the table identifier is treated as a table identifier. Therefore, when you specify a scope variable in the FROM clause as a table identifier, qualify the scope variable with a schema name.

To Page Top

(2) The effective scope of the table to be updated (scope variable) in an UPDATE statement

The effective scope of the scope variable encompasses the SET clause of the UPDATE statement, its search conditions, and the search conditions in any subqueries within those search conditions. The following figure shows an example.

Figure 6‒15: Example of the effective scope of scope variable T1 specified in an UPDATE statement

[Figure]

Explanation

  1. The scope variable T1 can be referenced in the UPDATE statement's SET clause and search conditions.

  2. The scope variable T1 cannot be referenced outside the search condition portion of the subquery.

  3. The scope variable T1 can be referenced in the search conditions of subqueries in the SET clause of the UPDATE statement.

  4. The scope variable T1 can be referenced in the UPDATE statement's SET clause and search conditions.

  5. The scope variable T1 cannot be referenced outside the search condition portion of the subquery.

  6. The scope variable T1 can be referenced in the search conditions of subqueries in the search conditions of the UPDATE statement.

To Page Top

(3) The effective scope of the table from which data is to be deleted (scope variable) in a DELETE statement

The effective scope of the scope variable encompasses the search conditions of the DELETE statement as well as the search conditions in any subqueries in those search conditions. The following figure shows an example.

Figure 6‒16: Example of the effective scope of scope variable T1 specified in a DELETE statement

[Figure]

Explanation

  1. The scope variable T1 can be referenced in the search conditions of the DELETE statement.

  2. The scope variable T1 cannot be referenced outside of the search condition portion of the subquery.

  3. The scope variable T1 can be referenced in the search conditions of subqueries in the search conditions of the DELETE statement.

To Page Top

(4) The effective scope of the table into which data is to be inserted into (non-scope variable) in an INSERT statement

The table that is the target of the INSERT statement does not have effective scope anywhere inside the insertion value (including subqueries) or the query expression body. This is illustrated in the following example.

Example
INSERT INTO "T1"     ...1
    VALUES(
            (SELECT "C1" FROM "T3"
                 WHERE "C2">="C3"     ...2
            )
           )
Explanation

The underlined insertion target table does not have effective scope anywhere inside the INSERT statement.

The insertion target table T1 cannot be referenced even in the search condition portion of the subquery.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.