friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.db2_5.4.0.1/rbafzmstch2col.htm

323 lines
23 KiB
HTML
Raw Permalink Normal View History

Add readme and dist folders
2024-04-02 14:02:31 +00:00
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="dc.language" scheme="rfc1766" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow"/>
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="keywords" content="qualified, name, column, name qualification,
COMMENT statement, qualification of column names, qualifying a column name,
correlation name, synonym for qualifying a column name, defining, object table,
designator, table, ambiguous reference, undefined reference, description,
subquery, correlated reference, lateral correlation" />
<title>Column names</title>
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
<link rel="stylesheet" type="text/css" href="ic.css" />
</head>
<body>
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
<a name="ch2col"></a>
<h2 id="ch2col"><a href="rbafzmst02.htm#ToC_227">Column names</a></h2><a id="idx576" name="idx576"></a>
<p>The meaning of a column name depends on its context. A column name can
be used to: </p>
<ul>
<li>Declare the name of a column, as in a CREATE TABLE statement.</li>
<li>Identify a column, as in a CREATE INDEX statement.</li>
<li>Specify values of the column, as in the following contexts:
<ul>
<li>In a <span class="italic">aggregate function</span> a column name
specifies all values of the column in the group or intermediate result table
to which the function is applied. Groups and intermediate result tables are
explained under <a href="rbafzmstsubsel.htm#subsel">Queries</a>. For example, MAX(SALARY) applies the
function MAX to all values of the column SALARY in a group.</li>
<li>In a <span class="italic">GROUP BY or ORDER BY clause,</span> a column name
specifies all values in the intermediate result table to which the clause
is applied. For example, ORDER BY DEPT orders an intermediate result table
by the values of the column DEPT.</li>
<li>In an <span class="italic">expression,</span> a <span class="italic">search
condition,</span> or a <span class="italic">scalar function,</span> a column name
specifies a value for each row or group to which the construct is applied.
For example, when the search condition CODE = 20 is applied to some row, the
value specified by the column name CODE is the value of the column CODE in
that row.</li></ul></li>
<li>Provide a column name for an expression to temporarily rename
a column, as in the <var class="pv">correlation-clause</var> of a <var class="pv">table-reference</var> in
a FROM clause, or in the AS clause in the <var class="pv">select-clause</var>.</li></ul>
<a name="cnqua"></a>
<h3 id="cnqua"><a href="rbafzmst02.htm#ToC_228">Qualified column names</a></h3><a id="idx577" name="idx577"></a>
<p>A qualifier for a column name can be a table name, a view name, an alias
name, or a correlation name.</p>
<p>Whether a column name can be qualified depends on its context: </p>
<ul>
<li>In the COMMENT and LABEL statements, the column name must be qualified.</li>
<li>Where the column name specifies values of the column, a column name can
be qualified.</li>
<li>In the <var class="pv">assignment-clause</var> of an UPDATE statement, it may be qualified.</li>
<li>In the <var class="pv">column-list</var> of an INSERT statement, it may be qualified.</li>
<li>In all other contexts, a column name must not be qualified.</li></ul>
<p>Where a qualifier is optional it can serve two purposes. See <a href="rbafzmstch2col.htm#qcn1">Column name qualifiers to avoid ambiguity</a> and <a href="rbafzmstch2col.htm#c2corr">Column name qualifiers in correlated references</a> for details.</p>
<a name="c2cornm"></a>
<h3 id="c2cornm"><a href="rbafzmst02.htm#ToC_229">Correlation names</a></h3><a id="idx578" name="idx578"></a><a id="idx579" name="idx579"></a><a id="idx580" name="idx580"></a><a id="idx581" name="idx581"></a>
<p>A <span class="italic">correlation name</span> can be defined in the FROM clause
of a query and after the target <var class="pv">table-name</var> or <var class="pv">view-name</var> in
an UPDATE or DELETE statement. For example, the clause shown below establishes
Z as a correlation name for X.MYTABLE: </p>
<pre class="xmp"> <span class="bold">FROM</span> X.MYTABLE Z</pre>
<p>A correlation name is associated with a table or view only within the context
in which it is defined. Hence, the same correlation name can be defined for
different purposes in different statements, or in different clauses of the
same statement.</p>
<p>As a qualifier, a correlation name can be used to avoid ambiguity or to
establish a correlated reference. A correlation name can also be used as a
shorter name for a table or view. In the example that is shown above, Z might
have been used merely to avoid having to enter X.MYTABLE more than once.</p>
<p>If a correlation name is specified for a table or view, any qualified reference
to a column of that instance of the table or view must use the correlation
name, rather than the table name or view name. For example, the reference
to EMPLOYEE.PROJECT in the following example is incorrect, because a correlation
name has been specified for EMPLOYEE: </p>
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE E ***INCORRECT***
<span class="bold">WHERE</span> EMPLOYEE.PROJECT='ABC'
</pre>
<p>The qualified reference to PROJECT should instead use the correlation name, &quot;E&quot;,
as shown below: </p>
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE E
&nbsp;&nbsp;<span class="bold">WHERE</span> E.PROJECT='ABC'</pre>
<p>Names specified in a FROM clause are either <span class="italic">exposed</span> or <span class="italic">non-exposed</span>. A correlation name is always
an exposed name. A table name or view name is said to be <span class="italic">exposed</span> in that FROM clause if a correlation name is not specified. For
example, in the following FROM clause, a correlation name is specified for
EMPLOYEE but not for DEPARTMENT, so DEPARTMENT is an exposed name, and EMPLOYEE
is not: </p>
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE E, DEPARTMENT</pre>
<p>A table name or view name that is exposed in a FROM clause must not be
the same as any other table name or view name exposed in that FROM clause
or any correlation name in the FROM clause. The names are compared after qualifying
any unqualified table or view names.</p>
<p>The first two FROM clauses shown below are correct, because each one contains
no more than one reference to EMPLOYEE that is exposed: </p>
<ol type="1">
<li>Given the FROM clause:
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE E1, EMPLOYEE</pre> a qualified reference such as EMPLOYEE.PROJECT denotes a column of
the second instance of EMPLOYEE in the FROM clause. A qualified reference
to the first instance of EMPLOYEE must use the correlation name &quot;E1&quot;
(E1.PROJECT).</li>
<li>Given the FROM clause:
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE, EMPLOYEE E2</pre> a qualified reference such as EMPLOYEE.PROJECT denotes a column of
the first instance of EMPLOYEE in the FROM clause. A qualified reference to
the second instance of EMPLOYEE must use the correlation name &quot;E2&quot;
(E2.PROJECT).</li>
<li>Given the FROM clause:
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE, EMPLOYEE ***INCORRECT***
</pre> the two exposed table names included in this clause (EMPLOYEE and
EMPLOYEE) are the same, and this is not allowed.</li>
<li>Given the following statement:
<pre class="xmp"> <span class="bold">SELECT</span> *
<span class="bold">FROM</span> EMPLOYEE E1, EMPLOYEE E2 ***INCORRECT***
<span class="bold">WHERE</span> EMPLOYEE.PROJECT='ABC'</pre>the qualified
reference EMPLOYEE.PROJECT is incorrect, because both instances of EMPLOYEE
in the FROM clause have correlation names. Instead, references to PROJECT
must be qualified with either correlation name (E1.PROJECT or E2.PROJECT).</li>
<li>Given the FROM clause:
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE, X.EMPLOYEE</pre> a reference to a column in the second instance of EMPLOYEE must use
X.EMPLOYEE (X.EMPLOYEE.PROJECT). This FROM clause is only valid if the authorization
ID of the statement is not X.</li></ol>
<p>A correlation name specified in a FROM clause must not be the same as: </p>
<ul>
<li>Any other correlation name in that FROM clause</li>
<li>Any unqualified table name or view name exposed in the FROM clause</li>
<li>The second SQL identifier of any qualified table name or view name that
is exposed in the FROM clause.</li></ul>
<p>For example, the following FROM clauses are incorrect: </p>
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE E, EMPLOYEE E
<span class="bold">FROM</span> EMPLOYEE DEPARTMENT, DEPARTMENT ***INCORRECT***
<span class="bold">FROM</span> X.T1, EMPLOYEE T1</pre>
<p>The following FROM clause is technically correct, though potentially confusing: </p>
<pre class="xmp"> <span class="bold">FROM</span> EMPLOYEE DEPARTMENT, DEPARTMENT EMPLOYEE</pre>
<p>The use of a correlation name in the FROM clause also allows the option
of specifying a list of column names to be associated with the columns of
the result table. As with a correlation name, these listed column names become
the exposed names of the columns that must be used for references to the columns
throughout the query. If a column name list is specified, then the column
names of the underlying table become non-exposed.</p>
<p>Given the FROM clause:</p>
<pre class="xmp"> <span class="bold">FROM</span> DEPARTMENT D <span class="bold">(</span>NUM,NAME,MGR,ANUM,LOC<span class="bold">)</span>
&nbsp;</pre>
<p>a qualified reference such as D.NUM denotes the first column of the DEPARTMENT
table that is defined in the table as DEPTNO. A reference to D.DEPTNO using
this FROM clause is incorrect since the column name DEPTNO is a non-exposed
column name.</p>
<p>If a list of columns is specified, it must consist of as many names as
there are columns in the <span class="italic">table-reference</span>. Each column
name must be unique and unqualified.</p>
<a name="qcn1"></a>
<h3 id="qcn1"><a href="rbafzmst02.htm#ToC_230">Column name qualifiers to avoid ambiguity</a></h3>
<p>In the context of a function, a GROUP BY clause, an ORDER BY clause, an
expression, or a search condition, a column name refers to values of a column
in some target table or view in a DELETE or UPDATE statement or <var class="pv">table-reference</var> in a FROM clause. The tables, views, and <var class="pv">table-reference</var>s <sup class="fn"><a id="wq312" name="wq312" href="rbafzmstch2col.htm#wq313">31</a></sup> that might contain the
column are called the <span class="italic">object tables</span> of the context. <a id="idx582" name="idx582"></a>Two or more object tables might contain
columns with the same name. One reason for qualifying a column name is to
designate the object from which the column comes. For information on avoiding
ambiguity between SQL parameters and variables and column names, see <a href="rbafzmstrefsqlv.htm#refsqlv">References to SQL parameters and SQL variables</a>.</p><a id="idx583" name="idx583"></a>
<p>A nested table expression which is preceded by a LATERAL or
TABLE keyword will consider <var class="pv">table-reference</var>s that precede it in the
FROM clause as object tables. The <var class="pv">table-reference</var>s that follow the
nested table expression are not considered as object tables.</p>
<a name="tdjm"></a>
<h4 id="tdjm">Table designators</h4><a id="idx584" name="idx584"></a><a id="idx585" name="idx585"></a>
<p>A qualifier that designates a specific object table is called a <span class="italic">table designator</span>. The clause that identifies the object tables also establishes
the table designators for them. For example, the object tables of an expression
in a SELECT clause are named in the FROM clause that follows it: </p>
<pre class="xmp"> <span class="bold">SELECT</span> CORZ.COLA, OWNY.MYTABLE.COLA
&nbsp;&nbsp;<span class="bold">FROM</span> OWNX.MYTABLE CORZ, OWNY.MYTABLE</pre>
<p>Table designators in the FROM clause are established as follows:</p>
<ul>
<li>A name that follows a table or view name is both a correlation name and
a table designator. Thus, CORZ is a table designator. CORZ is used to qualify
the first column name in the select list.</li>
<li>In SQL naming, an exposed table or view name is a table designator. Thus,
OWNY.MYTABLE is a table designator. OWNY.MYTABLE is used to qualify the second
column name in the select list.</li>
<li>In system naming, the table designator for an exposed table or view name
is the unqualified table or view name. In the following example MYTABLE is
the table designator for OWNY/MYTABLE.
<pre class="xmp"> <span class="bold">SELECT</span> CORZ.COLA, MYTABLE.COLA
&nbsp;&nbsp;<span class="bold">FROM</span> OWNX/MYTABLE CORZ, OWNY/MYTABLE</pre></li></ul><p class="indatacontent">Two or more object tables can be instances of the same table. In this
case, distinct correlation names must be used to unambiguously designate the
particular instances of the table. In the following FROM clause, X and Y
are defined to refer, respectively, to the first and second instances of
the table EMPLOYEE: </p>
<pre class="xmp"> <span class="bold">SELECT * FROM</span> EMPLOYEE X,EMPLOYEE Y</pre>
<a name="wq314"></a>
<h4 id="wq314">Avoiding undefined or ambiguous references</h4><a id="idx586" name="idx586"></a><a id="idx587" name="idx587"></a>
<p>When a column name refers to values of a column, it must be possible to
resolve that column name to exactly one object table. The following situations
are considered errors: </p>
<ul>
<li>No object table contains a column with the specified name. The reference
is undefined.</li>
<li>The column name is qualified by a table designator, but the table designated
does not include a column with the specified name. Again the reference is
undefined.</li>
<li>The name is unqualified and more than one object table includes a column
with that name. The reference is ambiguous.</li>
<li>The column name is qualified by a table designator, but the table designated
is not unique in the FROM clause and both occurrences of the designated table
include the column. The reference is ambiguous.</li>
<li>The column name is in a nested table expression which is not
preceded by the LATERAL or TABLE keyword or a table function or nested table
expression that is the right operand of a right outer join or a right exception
join and the column name does not refer to a column of a <span class="italic">table-reference</span> within the nested table expression's fullselect. The
reference is undefined.</li></ul>
<p>Avoid ambiguous references by qualifying a column name with a uniquely
defined table designator. If the column is contained in several object tables
with different names, the object table names can be used as designators. Ambiguous
references can also be avoided without the use of the table designator by
giving unique names to the columns of one of the object tables using the column
name list following the correlation name.</p>
<p>When qualifying a column with the exposed table name form of a table designator,
either the qualified or unqualified form of the exposed table name may be
used. However, the qualifier used and the table used must be the same after
fully qualifying the table name or view name and the table designator. </p>
<ol type="1">
<li>If the authorization ID of the statement is CORPDATA, then:
<pre class="xmp"> <span class="bold">SELECT</span> CORPDATA.EMPLOYEE.WORKDEPT
&nbsp;&nbsp;<span class="bold">FROM</span> EMPLOYEE</pre> is a valid statement.</li>
<li>If the authorization ID of the statement is REGION, then:
<pre class="xmp"> <span class="bold">SELECT</span> CORPDATA.EMPLOYEE.WORKDEPT
<span class="bold">FROM</span> EMPLOYEE ***INCORRECT***</pre>is invalid, because EMPLOYEE represents the table REGION.EMPLOYEE, but
the qualifier for WORKDEPT represents a different table, CORPDATA.EMPLOYEE.</li>
<li>If the authorization ID of the statement is REGION, then:
<pre class="xmp"> <span class="bold">SELECT</span> EMPLOYEE.WORKDEPT
<span class="bold">FROM</span> CORPDATA.EMPLOYEE ***INCORRECT***</pre>is invalid, because EMPLOYEE in the select list represents the table
REGION.EMPLOYEE, but the explicitly qualified table name in the FROM clause
represents a different table, CORPDATA.EMPLOYEE. In this case, either omit
the table qualifier in the select list, or define a correlation name for the
table designator in the FROM clause and use that correlation name as the qualifier
for column names in the statement.</li></ol>
<a name="c2corr"></a>
<h3 id="c2corr"><a href="rbafzmst02.htm#ToC_233">Column name qualifiers in correlated references</a></h3><a id="idx588" name="idx588"></a><a id="idx589" name="idx589"></a><a id="idx590" name="idx590"></a>
<p>A <span class="italic">subselect</span> is a form of a query that can be used
as a component of various SQL statements. Refer to <a href="rbafzmstsubsel.htm#subsel">Queries</a> for
more information about subselects. A <span class="italic">subquery</span> is a
form of a fullselect that is enclosed within parentheses. For example, a <span class="italic">subquery</span> can be used in a search condition. A fullselect
used in the FROM clause of a query is called a <span class="italic">nested table
expression</span>.</p>
<p>A subquery can include search conditions of its own, and these search conditions
can, in turn, include subqueries. Therefore, an SQL statement can contain
a hierarchy of subqueries. Those elements of the hierarchy that contain subqueries
are said to be at a higher level than the subqueries they contain.</p>
<p>Every element of the hierarchy has a clause that establishes one or more
table designators. This is the FROM clause, except in the highest level of
an UPDATE or DELETE statement. A search condition, the select list, the join
clause, an argument of a table function in a subquery, or a <span class="italic">nested table expression</span> that is preceded by the LATERAL keyword can reference
not only columns of the tables identified by the FROM clause of its own element
of the hierarchy, but also columns of tables identified at any level along
the path from its own element to the highest level of the hierarchy. A reference
to a column of a table identified at a higher level is called a <span class="italic">correlated reference</span>. A reference to a column of a table identified at
the same level from a <span class="italic">nested table expression</span> through
the use of the LATERAL keyword is called <span class="italic">lateral correlation</span>.</p>
<p>A correlated reference to column C of table T can be of the form C, T.C,
or Q.C, if Q is a correlation name defined for T. However, a correlated reference
in the form of an unqualified column name is not good practice. The following
explanation is based on the assumption that a correlated reference is always
in the form of a qualified column name and that the qualifier is a correlation
name.</p>
<p>Q.C is a correlated reference only if these three conditions are met: </p>
<ul>
<li>Q.C is used in a search condition, select list, join clause, or an argument
of a table function in a subquery.</li>
<li>Q does not designate a table used in the FROM clause of that subquery,
select list, join clause, or an argument of a table function in a subquery.</li>
<li>Q does designate a table used at some higher level.</li></ul><p class="indatacontent"> Q.C refers to column C of the table or view at the level where Q is
used as the table designator of that table or view. Because the same table
or view can be identified at many levels, unique correlation names are recommended
as table designators. If Q is used to designate a table at more than one level,
Q.C refers to the lowest level that contains the subquery that includes Q.C.</p>
<p>In the following statement, Q is used as a correlation name for T1 and
T2, but Q.C refers to the correlation name associated with T2, because it
is the lowest level that contains the subquery that includes Q.C. </p>
<pre class="xmp"><span class="bold">SELECT</span> *
<span class="bold">FROM</span> T1 Q
<span class="bold">WHERE</span> A &lt; <span class="bold">ALL (SELECT</span> B
<span class="bold">FROM</span> T2 Q
<span class="bold">WHERE</span> B &lt; <span class="bold">ANY (SELECT</span> D
<span class="bold">FROM</span> T3
<span class="bold">WHERE</span> D = Q.C))
</pre>
<a name="wq316"></a>
<h3 id="wq316"><a href="rbafzmst02.htm#ToC_234">Unqualified column names in correlated references</a></h3>
<p>An unqualified column name can also be a correlated reference if the column: </p>
<ul>
<li>Is used in a search condition of a subquery</li>
<li>Is not contained in a table used in the FROM clause of that subquery</li>
<li>Is contained in a table used at some higher level</li></ul><p class="indatacontent"> Unqualified correlated references are not recommended because it makes
the SQL statement difficult to understand. The column will be implicitly qualified
when the statement is prepared depending on which table the column was found
in. Once this implicit qualification is determined it will not change until
the statement is re-prepared. When an SQL statement that has an unqualified
correlated reference is prepared or executed, a warning is returned (SQLSTATE
01545).</p>
<hr /><div class="fnnum"><a id="wq313" name="wq313" href="rbafzmstch2col.htm#wq312">31</a>.</div>
<div class="fntext">In the case of a <var class="pv">joined-table</var>, each <var class="pv">table-reference</var> within
the <var class="pv">joined-table</var> is an object table.</div>
<br />
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstspecreg.htm">Previous Page</a> | <a href="rbafzmstch2refvar.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
<a href="rbafzmstindex.htm#index">Index</a> ]
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
</body>
</html>
Reference in New Issue Copy Permalink