Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
JOIN USING problem
54 views
Skip to first unread message
Jonathan Ball
May 4, 2012, 6:00:26 PM5/4/12
to
I have a program I created at V5R4 with a statement like the following:
select col1, col2, a.col3, a.col4, col5, col6
from table1 a join table2 b using (col3, col4)
where [selection criteria mainly against table2 columns]
When I attempted to run it after a recent upgrade to V6R1, the program
failed. The error message indicated that columns col3 and col4 weren't
found in table1.
After a lot of search, I found a mention of a change in the USING clause
in the "Memo to Users" for V6R1. It said that in compliance with the
SQL standard, columns in the SELECT clause that are also in the USING
clause may not be qualified by a table alias. (There is no
documentation of this in the V6R1 SQL reference that I could find, but
it is very clearly spelled out in the "Memo to Users".)
All fine and good, but now, having corrected the statement to:
select col1, col2, col3, col4, col5, col6
from table1 a join table2 b using (col3, col4)
where [...]
I am running into a different problem at compile time. If I have the
schema (library) containing the tables in my library list at compile
time, the SQL precompiler throws an error SQL0203 saying that one of the
common columns (col3 or col4) is "ambiguous." This is exactly why the
columns had to be qualified with the table alias at V5R4. However, if I
qualify the column as as above, to remove the ambiguity, the precompile
fails as well.
This seems like a bug to me. Has anyone else encountered it?
select col1, col2, a.col3, a.col4, col5, col6
from table1 a join table2 b using (col3, col4)
where [selection criteria mainly against table2 columns]
When I attempted to run it after a recent upgrade to V6R1, the program
failed. The error message indicated that columns col3 and col4 weren't
found in table1.
After a lot of search, I found a mention of a change in the USING clause
in the "Memo to Users" for V6R1. It said that in compliance with the
SQL standard, columns in the SELECT clause that are also in the USING
clause may not be qualified by a table alias. (There is no
documentation of this in the V6R1 SQL reference that I could find, but
it is very clearly spelled out in the "Memo to Users".)
All fine and good, but now, having corrected the statement to:
select col1, col2, col3, col4, col5, col6
from table1 a join table2 b using (col3, col4)
where [...]
I am running into a different problem at compile time. If I have the
schema (library) containing the tables in my library list at compile
time, the SQL precompiler throws an error SQL0203 saying that one of the
common columns (col3 or col4) is "ambiguous." This is exactly why the
columns had to be qualified with the table alias at V5R4. However, if I
qualify the column as as above, to remove the ambiguity, the precompile
fails as well.
This seems like a bug to me. Has anyone else encountered it?
CRPence
May 4, 2012, 6:13:44 PM5/4/12
to
On 04 May 2012 15:00, Jonathan Ball wrote:
> <<SNIP>>
Regards, Chuck
> <<SNIP>>
>
> I am running into a different problem at compile time. If I have the
> schema (library) containing the tables in my library list at compile
> time, the SQL precompiler throws an error SQL0203 saying that one of
> the common columns (col3 or col4) is "ambiguous." This is exactly
> why the columns had to be qualified with the table alias at V5R4.
> However, if I qualify the column as as above, to remove the
> ambiguity, the precompile fails as well.
>
> This seems like a bug to me. Has anyone else encountered it?
https://www.google.com/search?q=msgsql0203+join+using
> I am running into a different problem at compile time. If I have the
> schema (library) containing the tables in my library list at compile
> time, the SQL precompiler throws an error SQL0203 saying that one of
> the common columns (col3 or col4) is "ambiguous." This is exactly
> why the columns had to be qualified with the table alias at V5R4.
> However, if I qualify the column as as above, to remove the
> ambiguity, the precompile fails as well.
>
> This seems like a bug to me. Has anyone else encountered it?
Regards, Chuck
CRPence
May 4, 2012, 7:20:10 PM5/4/12
to
On 04 May 2012 15:13, CRPence wrote:
> On 04 May 2012 15:00, Jonathan Ball wrote:
>> <<SNIP>> upgrade to V6R1 <<SNIP>>
> On 04 May 2012 15:00, Jonathan Ball wrote:
looking at the PSP document for v7r1m0, found in the above web search,
so initially I just sent the above.
If the PTF level is already c1256610 and\or latest DB Fixpack [i.e.
SI42050 applied or superseded], then my SWAG is that very probably the
v7r1 APAR SE50797 "OSP-DB-OTHER-MSGSQL0203 REFERENCING A FIELD IN A JOIN
USING CLAUSE" which appears on the IBM i 7.1 PSP is likely going to have
a corresponding APAR for IBM i 6.1; i.e. perhaps what got that fix in
the 7.1 PSP listing, would be the same origin in the prior release, and
similarly require a "replacement" PTF.
Seems the v6r1 APAR SE46048 "OSP-DB-OTHER-MSGSQL0203
OSP-DB-OTHER-MSGSQL0203" giving SI42050 correlates to the v7r1 PTF
SI42092 [on C1116710] for APAR SE46048. The latter was the PTF that was
replaced in v7r1 with PTF SI43721 for SE50797. If so, such a v6r1 PTF
may exist already, available for test; unavailable on the web, as would
be the case for the new APAR for different version of the OS.
Regardless, probably best to call your service provider; probably ask
directly if such a [test] PTF is available from a 6.1 APAR [as a
SYSROUTE] from the 7.1 APAR SE50797.
Regards, Chuck
George Plimpton
May 5, 2012, 9:18:28 PM5/5/12
to
corresponding APAR or PTF for V6R1.
CRPence
May 5, 2012, 11:36:58 PM5/5/12
to
On 05 May 2012 18:18, George Plimpton wrote:
> I found most of those, but they're all for V7R1; I can't find a
> corresponding APAR or PTF for V6R1.
Perhaps the NewsServer being used missed my other message which
> I found most of those, but they're all for V7R1; I can't find a
> corresponding APAR or PTF for V6R1.
explains further; both why I responded with only the search, and about
how I expect there is a v6r1 APAR\PTF likely [to be] in the pipeline,
but that contacting a service provider may be required to get access
pending General Availability:
https://groups.google.com/d/msg/comp.sys.ibm.as400.misc/37QzbmzvmbI/rwk1yHWClNUJ
Regards, Chuck
0 new messages