Discussion:
SQL0203 - Name xxxx is ambiguous, when using JOIN USING
(too old to reply)
Charles Wilt
2014-10-16 19:18:22 UTC
Permalink
Raw Message
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Luis Rodriguez
2014-10-16 19:24:17 UTC
Permalink
Raw Message
Charles,


SQL does not know which "mykey" column you want to use. To avoid
ambiguities qualify your tables and use the ON expression, like in JOIN TBLC
ON TBLA.MYKEY = TBLC.MYKEY

HTH,

Luis

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


On Thu, Oct 16, 2014 at 2:48 PM, Charles Wilt <***@gmail.com>
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-***@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-***@midrange.com
> 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-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange
Alan Campin
2014-10-16 19:25:45 UTC
Permalink
Raw Message
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-***@public.gmane.org>
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-16 19:32:18 UTC
Permalink
Raw Message
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-***@public.gmane.org> 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-***@public.gmane.org>
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> > 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Luis Rodriguez
2014-10-16 19:55:43 UTC
Permalink
Raw Message
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 <***@gmail.com>
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 <***@gmail.com> 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 <***@gmail.com>
> > 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-***@midrange.com
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-***@midrange.com
> > > 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-***@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-***@midrange.com
> > 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-***@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-***@midrange.com
> 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-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archive
Charles Wilt
2014-10-17 02:20:48 UTC
Permalink
Raw Message
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 <***@gmail.com> 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 <***@gmail.com>
> 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 <***@gmail.com>
> 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 <***@gmail.com>
> > > 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-***@midrange.com
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > or email: MIDRANGE-L-***@midrange.com
> > > > 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-***@midrange.com
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-***@midrange.com
> > > 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-***@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-***@midrange.com
> > 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-***@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-***@midrange.com
> 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-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archives
at http://archive
Tom Stieger
2014-10-17 14:59:40 UTC
Permalink
Raw Message
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 <***@gmail.com>
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 <***@gmail.com>
> 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 <***@gmail.com>
> > 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 <***@gmail.com>
> > 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 <
> ***@gmail.com>
> > > > 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-***@midrange.com
> > > > > To subscribe, unsubscribe, or change list options,
> > > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > > or email: MIDRANGE-L-***@midrange.com
> > > > > 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-***@midrange.com
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > or email: MIDRANGE-L-***@midrange.com
> > > > 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-***@midrange.com
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-***@midrange.com
> > > 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-***@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-***@midrange.com
> > 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-***@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-***@midrange.com
> 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-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l
(WalzCraft) Jerry Forss
2014-10-16 19:25:06 UTC
Permalink
Raw Message
Do you need to qualify each table TA, TB, TC and use TA.mykey?

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of Charles Wilt
Sent: Thursday, October 16, 2014 2:18 PM
To: Midrange Systems Technical Discussion
Subject: SQL0203 - Name xxxx is ambiguous, when using JOIN USING

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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Subject to Change Notice:

WalzCraft reserves the right to improve designs, and to change specifications without notice.

Confidentiality Notice:

This message and any attachments may contain confidential and privileged information that is protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s) and should "only" pertain to "WalzCraft" company matters. If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this email or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this email in error, please notify the sender immediately and permanently delete this email. Thank You;

WalzCraft PO Box 1748 La Crosse, WI. 54602-1748
www.walzcraft.com Phone... 608-781-6355
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
CRPence
2014-10-17 15:58:20 UTC
Permalink
Raw Message
On 16-Oct-2014 14:18 -0500, Charles Wilt 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.
>
> <<SNIP>>
>
> Running 7.1 TR6

Does the following revision to the above query, with parentheses as
specified, enable the joined-table specification to use the "JOIN ...
USING" syntax for the next table-reference? As I recall, that is the
proper\expected syntax.
<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzjoinedt.htm>

select mykey, flda, fldb, fldc
from ( tbla
join tblb using (mykey)
) /* end joined-table 1 */
join tblc using (mykey)
/* end joined-table 2; non-parenthetical */

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-17 16:46:51 UTC
Permalink
Raw Message
Chuck,

Nope I've tried it.

Same error, same cursor position.

Charles

On Fri, Oct 17, 2014 at 11:58 AM, CRPence <CRPbottle-***@public.gmane.org> wrote:

> On 16-Oct-2014 14:18 -0500, Charles Wilt 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.
>>
>> <<SNIP>>
>>
>> Running 7.1 TR6
>>
>
> Does the following revision to the above query, with parentheses as
> specified, enable the joined-table specification to use the "JOIN ...
> USING" syntax for the next table-reference? As I recall, that is the
> proper\expected syntax.
> <http://www.ibm.com/support/knowledgecenter/api/content/
> ssw_ibm_i_71/db2/rbafzjoinedt.htm>
>
> select mykey, flda, fldb, fldc
> from ( tbla
> join tblb using (mykey)
> ) /* end joined-table 1 */
> join tblc using (mykey)
> /* end joined-table 2; non-parenthetical */
>
> --
> Regards, Chuck
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John Yeung
2014-10-17 17:35:19 UTC
Permalink
Raw Message
I'm with Tom Stieger: The amount of effort put into getting USING to
work, if it can even be made to work at all (like him, I've never been
able to get it to work), is WAAAY more than just switching to the
explicit ON syntax already, which in my opinion isn't that much less
elegant than USING.

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-17 18:35:24 UTC
Permalink
Raw Message
Obviously, for my immediate needs, I switched to what would work.

But from what I read, USING should work. In fact, if it doesn't work like
this it's pretty much worthless. I could have sworn that I've done this in
the past, but perhaps not...

I much prefer the USING syntax, especially when dealing with composite
keys. I just don't often get to use it since my legacy tables have a
prefix on the column name.

I've opened a PMR and we'll see what IBM says.

On Fri, Oct 17, 2014 at 1:35 PM, John Yeung <gallium.arsenide-***@public.gmane.org>
wrote:

> I'm with Tom Stieger: The amount of effort put into getting USING to
> work, if it can even be made to work at all (like him, I've never been
> able to get it to work), is WAAAY more than just switching to the
> explicit ON syntax already, which in my opinion isn't that much less
> elegant than USING.
>
> John Y.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Scott Mildenberger
2014-10-17 19:15:16 UTC
Permalink
Raw Message
Using Google I came across a link that implied you cannot use USING over 3 tables like you are trying to do in MySQL either. It had something to do with USING being expanded into a version using ON thus resulting in the same error you received.

Scott

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of Charles Wilt
Sent: Friday, October 17, 2014 12:35 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING

Obviously, for my immediate needs, I switched to what would work.

But from what I read, USING should work. In fact, if it doesn't work like this it's pretty much worthless. I could have sworn that I've done this in the past, but perhaps not...

I much prefer the USING syntax, especially when dealing with composite keys. I just don't often get to use it since my legacy tables have a prefix on the column name.

I've opened a PMR and we'll see what IBM says.

On Fri, Oct 17, 2014 at 1:35 PM, John Yeung <gallium.arsenide-***@public.gmane.org>
wrote:

> I'm with Tom Stieger: The amount of effort put into getting USING to
> work, if it can even be made to work at all (like him, I've never been
> able to get it to work), is WAAAY more than just switching to the
> explicit ON syntax already, which in my opinion isn't that much less
> elegant than USING.
>
> John Y.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Raul A. Jager W.
2014-10-17 19:36:28 UTC
Permalink
Raw Message
Try to put parentesis around the firsrt 2 tables, and join them to the
third.

Scott Mildenberger wrote:

>Using Google I came across a link that implied you cannot use USING over 3 tables like you are trying to do in MySQL either. It had something to do with USING being expanded into a version using ON thus resulting in the same error you received.
>
>Scott
>
>-----Original Message-----
>From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of Charles Wilt
>Sent: Friday, October 17, 2014 12:35 PM
>To: Midrange Systems Technical Discussion
>Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING
>
>Obviously, for my immediate needs, I switched to what would work.
>
>But from what I read, USING should work. In fact, if it doesn't work like this it's pretty much worthless. I could have sworn that I've done this in the past, but perhaps not...
>
>I much prefer the USING syntax, especially when dealing with composite keys. I just don't often get to use it since my legacy tables have a prefix on the column name.
>
>I've opened a PMR and we'll see what IBM says.
>
>On Fri, Oct 17, 2014 at 1:35 PM, John Yeung <gallium.arsenide-***@public.gmane.org>
>wrote:
>
>
>
>>I'm with Tom Stieger: The amount of effort put into getting USING to
>>work, if it can even be made to work at all (like him, I've never been
>>able to get it to work), is WAAAY more than just switching to the
>>explicit ON syntax already, which in my opinion isn't that much less
>>elegant than USING.
>>
>>John Y.
>>--
>>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
>>list To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>>unsubscribe, or change list options,
>>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
>
>
>
>

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-17 20:04:35 UTC
Permalink
Raw Message
Read the thread...that has been tried. :)

No effect.

Charles

On Fri, Oct 17, 2014 at 3:36 PM, Raul A. Jager W. <raul-txJ8x5cWuapVBtrROPlz/***@public.gmane.org> wrote:

> Try to put parentesis around the firsrt 2 tables, and join them to the
> third.
>
> Scott Mildenberger wrote:
>
> Using Google I came across a link that implied you cannot use USING over
>> 3 tables like you are trying to do in MySQL either. It had something to do
>> with USING being expanded into a version using ON thus resulting in the
>> same error you received.
>>
>> Scott
>>
>> -----Original Message-----
>> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>> Charles Wilt
>> Sent: Friday, October 17, 2014 12:35 PM
>> To: Midrange Systems Technical Discussion
>> Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING
>>
>> Obviously, for my immediate needs, I switched to what would work.
>>
>> But from what I read, USING should work. In fact, if it doesn't work
>> like this it's pretty much worthless. I could have sworn that I've done
>> this in the past, but perhaps not...
>>
>> I much prefer the USING syntax, especially when dealing with composite
>> keys. I just don't often get to use it since my legacy tables have a
>> prefix on the column name.
>>
>> I've opened a PMR and we'll see what IBM says.
>>
>> On Fri, Oct 17, 2014 at 1:35 PM, John Yeung <gallium.arsenide-***@public.gmane.org>
>> wrote:
>>
>>
>>
>>> I'm with Tom Stieger: The amount of effort put into getting USING to
>>> work, if it can even be made to work at all (like him, I've never been able
>>> to get it to work), is WAAAY more than just switching to the explicit ON
>>> syntax already, which in my opinion isn't that much less elegant than USING.
>>>
>>> John Y.
>>> --
>>> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
>>> list To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>>> unsubscribe, or change list options,
>>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>> unsubscribe, or change list options,
>> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org Before posting, please take a
>> moment to review the archives at http://archive.midrange.com/midrange-l.
>>
>>
>>
>>
>
> -- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
> -- Verificado por Anti-Virus Corporativo Symantec --
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Raul A. Jager W.
2014-10-17 20:37:39 UTC
Permalink
Raw Message
Sory, I miss that in the thread, but I tryed and it works (7.1)

Like this:
select * from (file1 join file2 using(key)) join file3 using(key)

Charles Wilt wrote:

>Read the thread...that has been tried. :)
>
>No effect.
>
>Charles
>
>On Fri, Oct 17, 2014 at 3:36 PM, Raul A. Jager W. <raul-txJ8x5cWuapVBtrROPlz/***@public.gmane.org> wrote:
>
>
>
>>Try to put parentesis around the firsrt 2 tables, and join them to the
>>third.
>>
>>Scott Mildenberger wrote:
>>
>> Using Google I came across a link that implied you cannot use USING over
>>
>>
>>>3 tables like you are trying to do in MySQL either. It had something to do
>>>with USING being expanded into a version using ON thus resulting in the
>>>same error you received.
>>>
>>>Scott
>>>
>>>-----Original Message-----
>>>From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
>>>Charles Wilt
>>>Sent: Friday, October 17, 2014 12:35 PM
>>>To: Midrange Systems Technical Discussion
>>>Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING
>>>
>>>Obviously, for my immediate needs, I switched to what would work.
>>>
>>>But from what I read, USING should work. In fact, if it doesn't work
>>>like this it's pretty much worthless. I could have sworn that I've done
>>>this in the past, but perhaps not...
>>>
>>>I much prefer the USING syntax, especially when dealing with composite
>>>keys. I just don't often get to use it since my legacy tables have a
>>>prefix on the column name.
>>>
>>>I've opened a PMR and we'll see what IBM says.
>>>
>>>On Fri, Oct 17, 2014 at 1:35 PM, John Yeung <gallium.arsenide-***@public.gmane.org>
>>>wrote:
>>>
>>>
>>>
>>>
>>>
>>>>I'm with Tom Stieger: The amount of effort put into getting USING to
>>>>work, if it can even be made to work at all (like him, I've never been able
>>>>to get it to work), is WAAAY more than just switching to the explicit ON
>>>>syntax already, which in my opinion isn't that much less elegant than USING.
>>>>
>>>>John Y.
>>>>--
>>>>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
>>>>list To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>>>>unsubscribe, or change list options,
>>>>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>>>or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
>>>unsubscribe, or change list options,
>>>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>>or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org Before posting, please take a
>>>moment to review the archives at http://archive.midrange.com/midrange-l.
>>>
>>>
>>>
>>>
>>>
>>>
>>-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
>> -- Verificado por Anti-Virus Corporativo Symantec --
>>
>>--
>>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>>To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>To subscribe, unsubscribe, or change list options,
>>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>>or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
>>Before posting, please take a moment to review the archives
>>at http://archive.midrange.com/midrange-l.
>>
>>
>>
>>

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-17 20:57:04 UTC
Permalink
Raw Message
​Interesting...

What TR and DB Group PTF are you on?​

On Fri, Oct 17, 2014 at 4:37 PM, Raul A. Jager W. <***@abc.com.py> wrote:

> Sory, I miss that in the thread, but I tryed and it works (7.1)
>
> Like this:
> select * from (file1 join file2 using(key)) join file3 using(key)
>
>
>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the ar
Scott Mildenberger
2014-10-17 21:09:36 UTC
Permalink
Raw Message
I just made a test case and it does not work for me at 7.1 DB2 group level 30.

create table scott.junk1 (key1 char(1))
create table scott.junk2 (key1 char(1))
create table scott.junk3 (key1 char(1))

select * from (junk1 join junk2 using(key1)) join junk3 using(key1)
Name KEY1 is ambiguous.

If it works for Raul at some other level of the DB2 group then definitely you have a case with IBM.

Scott


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-***@midrange.com] On Behalf Of Charles Wilt
Sent: Friday, October 17, 2014 2:57 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING

​Interesting...

What TR and DB Group PTF are you on?​

On Fri, Oct 17, 2014 at 4:37 PM, Raul A. Jager W. <***@abc.com.py> wrote:

> Sory, I miss that in the thread, but I tryed and it works (7.1)
>
> Like this:
> select * from (file1 join file2 using(key)) join file3 using(key)
>
>
>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-***@midrange.com To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com 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-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review t
Raul A Jager W
2014-10-17 22:02:15 UTC
Permalink
Raw Message
I found the diference, the second join uses a different key.

Here is what I use:

SELECT ncta, nclie, nro_docu, imp, f_venc, f_asiento, nasie, lasie
FROM (c.movi_f_cta JOIN c.saldo_act using(ncta)) left outer join
c.fact_pasa using(f_asiento, nasie, lasie) WHERE ncuent like '12__'

Ejecución completa de sentencia SELECT





On 10/17/2014 06:09 PM, Scott Mildenberger wrote:
> I just made a test case and it does not work for me at 7.1 DB2 group level 30.
>
> create table scott.junk1 (key1 char(1))
> create table scott.junk2 (key1 char(1))
> create table scott.junk3 (key1 char(1))
>
> select * from (junk1 join junk2 using(key1)) join junk3 using(key1)
> Name KEY1 is ambiguous.
>
> If it works for Raul at some other level of the DB2 group then definitely you have a case with IBM.
>
> Scott
>
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-***@midrange.com] On Behalf Of Charles Wilt
> Sent: Friday, October 17, 2014 2:57 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING
>
> ​Interesting...
>
> What TR and DB Group PTF are you on?​
>
> On Fri, Oct 17, 2014 at 4:37 PM, Raul A. Jager W. <***@abc.com.py> wrote:
>
>> Sory, I miss that in the thread, but I tryed and it works (7.1)
>>
>> Like this:
>> select * from (file1 join file2 using(key)) join file3 using(key)
>>
>>
>>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-***@midrange.com To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-***@midrange.com Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
>

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archives
at http:/
Raul A Jager W
2014-10-17 22:05:44 UTC
Permalink
Raw Message
There is a way:

with a as (select * from qtemp.test1 join qtemp.test2 using(key))
select * from a join qtemp.test3 using(key)
Ejecución completa de sentencia SELECT


On 10/17/2014 06:09 PM, Scott Mildenberger wrote:
> I just made a test case and it does not work for me at 7.1 DB2 group level 30.
>
> create table scott.junk1 (key1 char(1))
> create table scott.junk2 (key1 char(1))
> create table scott.junk3 (key1 char(1))
>
> select * from (junk1 join junk2 using(key1)) join junk3 using(key1)
> Name KEY1 is ambiguous.
>
> If it works for Raul at some other level of the DB2 group then definitely you have a case with IBM.
>
> Scott
>
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-***@midrange.com] On Behalf Of Charles Wilt
> Sent: Friday, October 17, 2014 2:57 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING
>
> ​Interesting...
>
> What TR and DB Group PTF are you on?​
>
> On Fri, Oct 17, 2014 at 4:37 PM, Raul A. Jager W. <***@abc.com.py> wrote:
>
>> Sory, I miss that in the thread, but I tryed and it works (7.1)
>>
>> Like this:
>> select * from (file1 join file2 using(key)) join file3 using(key)
>>
>>
>>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-***@midrange.com To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-***@midrange.com Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
>

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archive
Jim It
2014-10-17 22:22:18 UTC
Permalink
Raw Message
I would never advise anyone to use USING in any SQL statement. Seems nice, but not so much.

Jim
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Raul A. Jager W.
2014-10-17 21:43:34 UTC
Permalink
Raw Message
I'm very behind TR3

Charles Wilt wrote:

>​Interesting...
>
>What TR and DB Group PTF are you on?​
>
>On Fri, Oct 17, 2014 at 4:37 PM, Raul A. Jager W. <***@abc.com.py> wrote:
>
>
>
>>Sory, I miss that in the thread, but I tryed and it works (7.1)
>>
>>Like this:
>>select * from (file1 join file2 using(key)) join file3 using(key)
>>
>>
>>
>>
>>

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archives
at http://arc
r***@public.gmane.org
2014-10-20 11:09:54 UTC
Permalink
Raw Message
Was the parenthesis the same between the two attempts?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Raul A. Jager W." <***@abc.com.py>
To: Midrange Systems Technical Discussion <midrange-***@midrange.com>
Date: 10/17/2014 05:52 PM
Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN
USING
Sent by: "MIDRANGE-L" <midrange-l-***@midrange.com>



I'm very behind TR3

Charles Wilt wrote:

>​Interesting...
>
>What TR and DB Group PTF are you on?​
>
>On Fri, Oct 17, 2014 at 4:37 PM, Raul A. Jager W. <***@abc.com.py>
wrote:
>
>
>
>>Sory, I miss that in the thread, but I tryed and it works (7.1)
>>
>>Like this:
>>select * from (file1 join file2 using(key)) join file3 using(key)
>>
>>
>>
>>
>>

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
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-***@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-***@midrange.com
Before posting, please take a moment to review the archives
at
Charles Wilt
2014-10-17 20:05:54 UTC
Permalink
Raw Message
I did a little searching myself...

Ran across a couple different posts from me that seemed to indicate it was
working at some point..
http://archive.midrange.com/midrange-l/201103/msg00575.html
http://archive.midrange.com/rpg400-l/201010/msg00356.html

Charles

On Fri, Oct 17, 2014 at 3:15 PM, Scott Mildenberger <
SMildenberger-***@public.gmane.org> wrote:

> Using Google I came across a link that implied you cannot use USING over 3
> tables like you are trying to do in MySQL either. It had something to do
> with USING being expanded into a version using ON thus resulting in the
> same error you received.
>
> Scott
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org] On Behalf Of
> Charles Wilt
> Sent: Friday, October 17, 2014 12:35 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL0203 - Name xxxx is ambiguous, when using JOIN USING
>
> Obviously, for my immediate needs, I switched to what would work.
>
> But from what I read, USING should work. In fact, if it doesn't work like
> this it's pretty much worthless. I could have sworn that I've done this in
> the past, but perhaps not...
>
> I much prefer the USING syntax, especially when dealing with composite
> keys. I just don't often get to use it since my legacy tables have a
> prefix on the column name.
>
> I've opened a PMR and we'll see what IBM says.
>
> On Fri, Oct 17, 2014 at 1:35 PM, John Yeung <gallium.arsenide-***@public.gmane.org>
> wrote:
>
> > I'm with Tom Stieger: The amount of effort put into getting USING to
> > work, if it can even be made to work at all (like him, I've never been
> > able to get it to work), is WAAAY more than just switching to the
> > explicit ON syntax already, which in my opinion isn't that much less
> > elegant than USING.
> >
> > John Y.
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-21 21:02:24 UTC
Permalink
Raw Message
Just to update the list...

SQL Fiddle shows the query as working with Oracle 11gR2 and MySQL 5.6.6m9
http://sqlfiddle.com/#!4/ad8c0

Those of you on already on Stackoverflow may have seen my post there:
https://stackoverflow.com/questions/26493845/three-or-more-tables-join-using-common-key

Interestingly, neither DB2 10.5 for LUW nor DB2 10 for z/OS support the
JOIN USING syntax at all. So I guess it's nice to get something first, but
then of course you get to work the bugs out!

Charles



On Thu, Oct 16, 2014 at 3:18 PM, Charles Wilt <charles.wilt-***@public.gmane.org>
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John Yeung
2014-10-21 21:49:13 UTC
Permalink
Raw Message
On Tue, Oct 21, 2014 at 5:02 PM, Charles Wilt <charles.wilt-***@public.gmane.org> wrote:
> Interestingly, neither DB2 10.5 for LUW nor DB2 10 for z/OS support the
> JOIN USING syntax at all. So I guess it's nice to get something first, but
> then of course you get to work the bugs out!

I guess "nice" is one way to look at it.

I've always gotten the impression that DB2's flavor of SQL is more
conservative than others; that it strives to stick to "standard" SQL,
only reluctantly adding "optional standard" features (like USING) and
actively avoiding nonstandard features.

This is good and bad, but I think IBMers tend to focus on the good,
both because that's the database they've got, and because the IBM
culture tends to value conservatism.

I've been trying to go with this flow, and that's one reason why I
specifically recommended not bothering with USING and just using ON.
The other reason is that I prefer having fewer but more general
language constructs. For me, USING just doesn't feel like a big
enough improvement over ON to be worth keeping that extra keyword
around in my vocabulary, which I can't use anyway if I find myself
talking to certain other databases.

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-22 13:40:57 UTC
Permalink
Raw Message
John,

I don't use USING much myself, primarily since every system I've been on
has old school DDS defined files with a prefix character in the field names.

But my newer development does have common column names without prefixing,
and long ones at that. Not to mention composite keys. So when doing
ad-hoc queries, it's a lot nicer to be able to use USING when I can.

Charles

Charles

On Tue, Oct 21, 2014 at 5:49 PM, John Yeung <gallium.arsenide-***@public.gmane.org>
wrote:

> On Tue, Oct 21, 2014 at 5:02 PM, Charles Wilt <charles.wilt-***@public.gmane.org>
> wrote:
> > Interestingly, neither DB2 10.5 for LUW nor DB2 10 for z/OS support the
> > JOIN USING syntax at all. So I guess it's nice to get something first,
> but
> > then of course you get to work the bugs out!
>
> I guess "nice" is one way to look at it.
>
> I've always gotten the impression that DB2's flavor of SQL is more
> conservative than others; that it strives to stick to "standard" SQL,
> only reluctantly adding "optional standard" features (like USING) and
> actively avoiding nonstandard features.
>
> This is good and bad, but I think IBMers tend to focus on the good,
> both because that's the database they've got, and because the IBM
> culture tends to value conservatism.
>
> I've been trying to go with this flow, and that's one reason why I
> specifically recommended not bothering with USING and just using ON.
> The other reason is that I prefer having fewer but more general
> language constructs. For me, USING just doesn't feel like a big
> enough improvement over ON to be worth keeping that extra keyword
> around in my vocabulary, which I can't use anyway if I find myself
> talking to certain other databases.
>
> John Y.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
> 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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Loading...