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


  1.  Home
  2. MIDRANGE-L
  3. October 2014

Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING

Charles-

I don't recall if this is documented well, but the USING syntax has never
been able to do what you want. I have previously tested it in v5r4, 6.1 and
7.1 to no avail. It always left me frustrated. I finally gave up and went
with always using the ON syntax for joining so that I wouldn't have
problems later on.

Here is the relevant link to the SQL reference:
http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzjoinedt.htm?lang=en

One solution that may work (but in my opinion, probably isn't worth the
effort) is to use CTE's to get around this.

Example:
WITH cte1 as (
select mykey, flda, fldb
from tbla
join tblb using (mykey)
)
SELECT mykey, flda, fldb, fldc
from cte1
join tblc using (mykey)


Regards,

Tom Stieger

On Thu, Oct 16, 2014 at 7:20 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Luis,

Like any other operation, there's an order the join is done in. There
should only ever been two MYKEYs to join.

I tried adding parens to make the order explicit, but that had no effect.

It'd be interesting for somebody on 6.1 and/or somebody on 7.2 to try this.

Otherwise, I may just open a PMR.

Charles

On Thu, Oct 16, 2014 at 3:55 PM, Luis Rodriguez <luisro58@xxxxxxxxx>
wrote:

Charles,

I think that what you wrote is valid when there are two tables. If there
is
a third table, How can the SQL interpreter know which table are you
referring to?

In the V6R1 there is a reference to using parentheses when doing multiple
joins, in order to avoid ambiguities. Nevertheless, in the examples
provided they utilize the ON clause and qualify their column names...

Regards,
Luis

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--


On Thu, Oct 16, 2014 at 3:02 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

There shouldn't be multiple mykey columns...

Once Tblb is joined to tbla, there's only 1 mykey column, which should
be
available for joing to tblc USING mykey again...and the end result
should
have only 1 mykey column.

The USING cluase used to be broke and didn't work like this. IBM fixed
it
at 6.1. I was happy to hear it as it made the syntax I'm trying to use
work. Today it doesn't.

Charles


On Thu, Oct 16, 2014 at 3:25 PM, Alan Campin <alan0307d@xxxxxxxxx>
wrote:

You have to give them a reference and say which table the field is
coming
from.

select t1.mykey, t1.flda, t2.fldb, t3.fldc
from tbla t1
join tblb t2
on t1.mykey = t2.mykey
join tblc t3
on t1.mykey = t3.mykey


On Thu, Oct 16, 2014 at 1:18 PM, Charles Wilt <
charles.wilt@xxxxxxxxx>
wrote:

I'm getting SQL0203 - Name MYKEY is ambiguous
with the following:

select mykey, flda, fldb, fldc
from tbla
join tblb using (mykey)
join tblc using (mykey)
^---cursor positioned here...

I've tried adding parens (in various places), but get the same
error.

CREATE TABLE QTEMP/TBLA (MYKEY CHAR (10 ) NOT NULL WITH DEFAULT,
FLDA CHAR (10 ) NOT NULL WITH DEFAULT)

CREATE TABLE QTEMP/TBLB (MYKEY CHAR (10 ) NOT NULL WITH DEFAULT,
FLDB CHAR (10 ) NOT NULL WITH DEFAULT)

CREATE TABLE QTEMP/TBLC (MYKEY CHAR (10 ) NOT NULL WITH DEFAULT,
FLDC CHAR (10 ) NOT NULL WITH DEFAULT)

Running 7.1 TR6
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
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.