6.9.3 Effective scope of derived column names
The examples in the following tables illustrate the effective scope of derived column names.
Example SQL statement |
Effective scope |
||||
---|---|---|---|---|---|
C1 |
C2 |
C3 |
DC3 |
C4 |
|
SELECT "C1","C2" |
Y |
Y |
N |
Y |
N |
FROM "T1", |
Y |
Y |
N |
Y |
N |
(SELECT * |
N |
N |
Y |
N |
N |
FROM "T2" |
N |
N |
Y |
N |
N |
WHERE "C3"=100) |
N |
N |
Y |
N |
N |
"Y"("DC3") |
N |
N |
N |
Y |
N |
WHERE "C1"=100 AND |
Y |
Y |
N |
Y |
N |
"C2"=ANY |
Y |
Y |
N |
Y |
N |
(SELECT "C4" |
N |
N |
N |
N |
Y |
FROM "T3" |
Y |
Y |
N |
N |
Y |
WHERE "DC3"="C4") |
Y |
Y |
N |
Y |
Y |
- Legend:
-
Y: Has scope.
N: Does not have scope.
T1,T2,T3: Table identifier
C1, C2: T1 column name
C3: T2 column name
C4: T3 column name
Y: Correlation name
DC3: Derived column name of derived table Y
Example SQL statement |
Effective scope |
||||||
---|---|---|---|---|---|---|---|
C1 |
C2 |
GC2 |
C3 |
DC3 |
C4 |
C5 |
|
SELECT "C1","GC2" |
Y |
A |
Y |
N |
A |
N |
N |
FROM "T1", |
Y |
Y |
N |
N |
Y |
N |
N |
(SELECT * |
N |
N |
N |
Y |
N |
N |
N |
FROM "T2" |
N |
N |
N |
Y |
N |
N |
N |
WHERE "C3"=100) |
N |
N |
N |
Y |
N |
N |
N |
"Y"("DC3") |
N |
N |
N |
N |
Y |
N |
N |
WHERE "C1"=100 AND |
Y |
Y |
N |
N |
Y |
N |
N |
"C2"=ANY |
Y |
Y |
N |
N |
Y |
N |
N |
(SELECT "C4" |
N |
N |
N |
N |
N |
Y |
N |
FROM "T3" |
Y |
Y |
N |
N |
Y |
Y |
N |
WHERE "DC3"="C4") |
Y |
Y |
N |
N |
Y |
Y |
N |
GROUP BY "C1","C2"+100 AS "GC2" |
Y |
Y |
N |
N |
Y |
N |
N |
HAVING "C1"=100 AND |
Y |
A |
Y |
N |
A |
N |
N |
"GC2"=ANY |
Y |
A |
Y |
N |
A |
N |
N |
(SELECT "C5" |
N |
N |
N |
N |
N |
N |
Y |
FROM "T4" |
Y |
N |
N |
N |
N |
N |
Y |
WHERE SUM("DC3")="C5") |
Y |
A |
N |
N |
A |
N |
Y |
- Legend:
-
Y: Has scope.
A: Has scope, but is not a grouping column, so can only be specified as the argument of a set function.
N: Does not have scope.
T1,T2,T3,T4: Table identifier
C1, C2: T1 column name
C3: T2 column name
C4: T3 column name
C5: T4 column name
GC2: the names of the grouping columns
Y: Correlation name
DC3: Derived column name of derived table Y