× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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>>





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.