On 15 Sep 2013 10:14, Vernon Hamberg wrote:
<<SNIP>>
What you put in this post is flawed, I think - 3rd line ends with
OEIND - nothing after it, so something is missing, right?
The consistent truncation at position 46 on the two given lines of
the select-column-list in the most recent message, as compared with the
/same/ SQL text in the OP, presumably reveals what seems to be an
apparent copy\paste error. I expect that is just a transcription error
visible only in the quoted message, else I would have expected [whether
the original issue had been resolved or not], that the problem would
have transitioned to an SQL0104 "Token . was not valid. Valid tokens: ,
FROM INTO."; i.e. there is no comma between to apparent column names in
the select-list, so the next identifier is unexpected. So... I expect
that the truncated text is not an issue for the OP, and instead the OP
still suffers the same SQL5001 issue, just as was claimed in their most
recent message [left quoted, but trimmed, later in my message].
Here are the new\truncated text, and the original, with a scale line
to show the consistent truncation on the two lines; at position 46:
≥≥..._ SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEIND
≥≥..._ ADRESSAD.ADSFX#, OEINDLID.IDPRT#, OEINDLID.IDG
....._ ....+....1....+....2....+....3....+....4....+....5....+....6..
≥≥≥≥≥≥ SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEINDLID.IDCOM#,
≥≥≥≥≥≥ ADRESSAD.ADSFX#, OEINDLID.IDPRT#, OEINDLID.IDGRC# ...
That original -5001 issue is an attempt to use the unqualified
TABLE-name as a column qualifier, while the FROM-clause had fully
qualified the TABLE-name, yet no correlation-clause was specified for
that table-reference. If the table-reference is library-qualified [in
*SQL naming], then for lack of a correlation-name included on the
table-reference, the column-qualifier for those correlation-column
references also must use that same library-qualified naming. The
qualified table-designator is implicitly considered to be the
correlation-name. But instead of adding all of that library-qualified
naming to each column named, the easier option is to just specify as a
column qualifier, the same identifier that is explicitly specified as a
correlation-name in a correlation-clause of the table-reference; i.e. as
I described in my prior message:
http://archive.midrange.com/midrange-l/201309/msg00592.html
But after you check the help on the message, you'll want to take the
text at face value - somewhere in the statement is the OEINDLID that
the parser does not know about
Exactly. The text delimited by the double quotes are directly from
the "Cause..." for the message SQL5001 [from v5r3], then my comments for
each quoted sentence follow that:
- "If a correlation name is specified following the table name in a
FROM clause, the correlation name is considered to be the table
designator." No correlation name was specified for the table-reference
in the original query.
- "If a correlation name is not specified, the table name is
considered to be the table designator." Aha! But... the table name
*was* used as the column qualifier! Well... Yes, but...
- "If using SQL naming and the table is qualified with authorization
name, then the table designator is authorization-name.table-name."
Overlooking the misuse of "authorization name" in that text, and
recognizing that the original query explicitly specified the table name
qualified by its schema, the situation is described by this cause-text
in the message; i.e. If using SQL naming and the table is qualified with
a schema name, then the table-designator is schema-name.table-name.
FWiW a search of the InfoCenter on the following tokens presents a
good list of reading material: qualified column name
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzch2col.htm
_i Column names i_ <-- a parent topic to all but the last below
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcnqua.htm
_i Qualified column names i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzqcn1.htm
_i Column name qualifiers to avoid ambiguity i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzc2corr.htm
_i Column name qualifiers in correlated references i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzc2cornm.htm
_i Correlation names i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzunqualified.htm
_i Unqualified column names in correlated references i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzavoidref.htm
_i Avoiding undefined or ambiguous references i_
- it might be due to the rename to T01, it might be due to being
in a nested situation.
No. The two correlated references in the scalar subselect as
correlated subquery have the same problem as the other columns using the
unqualified TABLE-name as a correlation-id. The references to the
locally-scoped column names with the correlation-name T01 were correctly
specified.
You had all those parentheses around the various parts of the JOINs -
I don't know what release of the OS you are on, but those parentheses
take on more importance as to order in more current releases.
The parenthetical queries have always had the same importance. What
transpired on some release boundary [and documented in the MTU], was a
new requirement to more explicitly define join precedence in cases where
the query was potentially ambiguous without such precedence being
explicitly defined. Prior releases had been open to interpretation,
what the intention was of the coder of the query. That meant, when more
robust query-rewrite capability came with the query processor, queries
that had no explicit join precedence could be interpreted differently
now [since the enhanced query rewrite] than they had in the past,
*possibly causing different results* than in the past. Thus the user
was forcibly required to rewrite their own queries in the manner they
intended the query processor to understand them [consistently; for join
precedence].
Here is a link on how to syntax-check a statement using RUNSQLSTM -
it should work with a CREATE TABLE.
Seems a link failed to get pasted here?
There are also online SQL syntax checkers. I found one at
http://www.dpriver.com/pp/sqlformat.htm and put your original
statement into it and asked it to be formatted - it reported a
problem in line 25, position 1 - that's where I saw you needed an
AND.
A formatting utility may not be sophisticated enough to recognize the
syntax error that would remain with the original query, even after
correcting the missing logical operator in the WHERE clause.
I put the AND there and formatted again - it went fine.
And that proves, that SQL formatter, is not entirely helpful with
regard to resolving the SQL5001 error :-(
Regards, Chuck
On 9/15/2013 9:28 AM, Hoteltravelfundotcom wrote:
I added the AND at the end but it gives this message:
Column qualifier or table OEINDLID undefined. <ed: <-- SQL5001>
<<SNIP>>
SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEIND
ADRESSAD.ADSFX#, OEINDLID.IDPRT#, OEINDLID.IDG
cursor is on CREATE what does this message mean?
On Sun, Sep 15, 2013 at 10:16 AM, Hoteltravelfundotcom wrote:
<<SNIP>>
On Sun, Sep 15, 2013 at 10:10 AM, Vernon Hambergwrote:
You are missing an AND near the end, in the final WHERE <<SNIP>>
On 9/15/2013 8:51 AM, Hoteltravelfundotcom wrote:
<<SNIP>>
SELECT ICPRTMIA.IARCC9, OEINDLID.IDDOCD, OEINDLID.IDCOM#,
ADRESSAD.ADSFX#, OEINDLID.IDPRT#, OEINDLID.IDGRC#, OEINDLID.IDENT#,
<<SNIP>
, (SELECT
SUM (T01.IDNTU$)
FROM ASTDTA.OEINDLID T01
WHERE T01.IDCOM# = '001'
AND T01.IDDOCD = OEINDLID.IDDOCD
AND T01.IDINV# = OEINDLID.IDINV#
AND T01.IDPRLC LIKE 'F%'
) <<SNIP>>
midrange.com
