FULL OUTER JOIN

[Anthony Robinson]

I was actually just wondering if someone could possibly take a look
and tell me what I may be doing wrong in this query? I keep getting
ambiguous column errors and have no idea why...?

Thanks in advance!!!

SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
(SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT -
COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM
AIM.AIMRETRIEVEDITEM WHERE
(AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
AS NUMBERIMAGESAVAILABLE,
AIM.DELIVERYMETHOD.DELIVERYMETHOD,
AIM.DELIVERY.DELIVERYADDRESS,
AIM.DELIVERY.DELIVERYDATE,
S1.STATUS AS DELIVERYSTATUS,
AIM.DELIVERY.VALIDATIONSTATUSID
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
-- AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE,
AIM.CHANNEL,
--AIM.DELIVERY,
AIM.DELIVERYMETHOD,
AIM.STATUS S1,
aim.archiveretrieval full outer join aim.delivery on
aim.archiveretrieval.archiveretrievalid =
aim.delivery.archiveretrievalid
full outer join aim.delivery on aim.delivery.deliverymethodid =
aim.deliverymethod.deliverymethodid
WHERE
AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID =
AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
AND S1.STATUSID = AIM.DELIVERY.STATUSID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
AIM.DELIVERY.ARCHIVERETRIEVALID
AND AIM.DELIVERY.DELIVERYMETHODID =
AIM.DELIVERYMETHOD.DELIVERYMETHODID
AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP)
OR (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND AIM.AIMCONNECTION.USERID = 'cfradle'
AND AIM.CHANNEL.CHANNEL =
(SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION,
AIM.AIMCONNECTION WHERE
AIM.CHANNELSESSION.CHANNELSESSIONID =
AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = 2)
GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
S1.STATUS,
AIM.DELIVERYMETHOD.DELIVERYMETHOD,
AIM.DELIVERY.DELIVERYADDRESS,
AIM.DELIVERY.DELIVERYDATE,
AIM.DELIVERY.VALIDATIONSTATUSID
ORDER BY AIMRETRIEVALID
FOR FETCH ONLY WITH UR;

[Rhino]

"Anthony Robinson" <an*****@yahoo.com> wrote in message
news:d1**************************@posting.google.c om...

I was actually just wondering if someone could possibly take a look
and tell me what I may be doing wrong in this query? I keep getting
ambiguous column errors and have no idea why...?

Thanks in advance!!!

SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
(SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT -
COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM
AIM.AIMRETRIEVEDITEM WHERE
(AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
AS NUMBERIMAGESAVAILABLE,
AIM.DELIVERYMETHOD.DELIVERYMETHOD,
AIM.DELIVERY.DELIVERYADDRESS,
AIM.DELIVERY.DELIVERYDATE,
S1.STATUS AS DELIVERYSTATUS,
AIM.DELIVERY.VALIDATIONSTATUSID
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
-- AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE,
AIM.CHANNEL,
--AIM.DELIVERY,
AIM.DELIVERYMETHOD,
AIM.STATUS S1,
aim.archiveretrieval full outer join aim.delivery on
aim.archiveretrieval.archiveretrievalid =
aim.delivery.archiveretrievalid
full outer join aim.delivery on aim.delivery.deliverymethodid =
aim.deliverymethod.deliverymethodid
WHERE
AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID =
AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
AND S1.STATUSID = AIM.DELIVERY.STATUSID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
AIM.DELIVERY.ARCHIVERETRIEVALID
AND AIM.DELIVERY.DELIVERYMETHODID =
AIM.DELIVERYMETHOD.DELIVERYMETHODID
AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP)
OR (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND AIM.AIMCONNECTION.USERID = 'cfradle'
AND AIM.CHANNEL.CHANNEL =
(SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION,
AIM.AIMCONNECTION WHERE
AIM.CHANNELSESSION.CHANNELSESSIONID =
AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = 2)
GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
S1.STATUS,
AIM.DELIVERYMETHOD.DELIVERYMETHOD,
AIM.DELIVERY.DELIVERYADDRESS,
AIM.DELIVERY.DELIVERYDATE,
AIM.DELIVERY.VALIDATIONSTATUSID
ORDER BY AIMRETRIEVALID
FOR FETCH ONLY WITH UR;

I'm surprised you're not getting an error referring to the extreme size of
the query or an excessive number of tables being referenced; that may be the
longest query I've ever seen and I've seen (and written) some big ones ;-)

It's a little hard to digest this monster but one thing caught my eye: does
the AIM.STATUS table really have a column called STATUS? I don't often see
tables whose column names match the table names. When you use the column
name S1.STATUS do you perhaps mean S1.SOME_OTHER_COLUMN_NAME? Remember,
S1.STATUS will resolve to AIM.STATUS.STATUS where AIM is the schema, the
first STATUS is the table name, and the second status is the column name.

Also, in the subquery that starts "SELECT CHANNEL FROM ....", is the CHANNEL
column unique to one of the tables named in the subquery's FROM clause? If
not, that might be the source of the problem since the column name is
completely unqualified.

Something that might help make this query a bit more readable is to use
table name substitutions more: just as you identify S1 as a reference to
AIM.STATUS, if you used other short references for the other tables in the
FROM clause, you could shorten the references to the column names
considerably throughout the rest of the query.

Something else that is confusing is that your main FROM clause appears to
name 9 tables (and comment 2 others) but I'm only seeing joining conditions
(ON clauses) being expressed for AIM.ARCHIVERETRIEVAL and AIM.DELIVERY.
[AIM.ARCHIVERETRIEVAL seems to be getting outer joined to AIM.DELIVERY, then
to AIM.DELIVERY, on a different condition, again. Is that what you really
mean to do?] If I'm not mistaken, that means that you are using the "old
syntax" to do implicit natural joins on all the other pairs of tables and
putting their joins in the WHERE clause. As a point of style, I prefer to
state all of my joins explicitly using INNER JOIN or (LEFT/RIGHT/FULL) OUTER
JOIN and putting all of my join conditions in ON clauses within the FROM
clause, then reserving the WHERE clause for local predicates (predicates
that affect only a single table) alone. I find it easier to read queries
that way. If you were to try this, your problem might become more obvious to
you (and others).

Another technique I've found useful for debugging big queries is to comment
out sections that seem suspect and see if the problem goes away; that method
helps you find the area where the problem lies and gradually isolate the
error. For instance, if you commented half the tables in the FROM clause and
their joins, if the problem went away, you could reasonably assume that the
commented lines contained the ambiguous reference. It's not necessarily a
very quick way to find a problem but it might be faster than having to wait
for someone to reply to a newsgroup post.

Rhino

[Toke]

I have had problems with DB2 on iSeries. My conclusion was that FULL OUTER
JOIN isn't possible on DB2 for iSeries.

Toke

"Rhino" <rh****@NOSPAM.sympatico.ca> skrev i en meddelelse
news:Vr********************@news20.bellglobal.com. ..

"Anthony Robinson" <an*****@yahoo.com> wrote in message
news:d1**************************@posting.google.c om...

I was actually just wondering if someone could possibly take a look
and tell me what I may be doing wrong in this query? I keep getting
ambiguous column errors and have no idea why...?

Thanks in advance!!!

SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
(SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT -
COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM
AIM.AIMRETRIEVEDITEM WHERE
(AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
AS NUMBERIMAGESAVAILABLE,
AIM.DELIVERYMETHOD.DELIVERYMETHOD,
AIM.DELIVERY.DELIVERYADDRESS,
AIM.DELIVERY.DELIVERYDATE,
S1.STATUS AS DELIVERYSTATUS,
AIM.DELIVERY.VALIDATIONSTATUSID
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
-- AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE,
AIM.CHANNEL,
--AIM.DELIVERY,
AIM.DELIVERYMETHOD,
AIM.STATUS S1,
aim.archiveretrieval full outer join aim.delivery on
aim.archiveretrieval.archiveretrievalid =
aim.delivery.archiveretrievalid
full outer join aim.delivery on aim.delivery.deliverymethodid =
aim.deliverymethod.deliverymethodid
WHERE
AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
AND AIM.AIMRETRIEVAL.AIMRETRIEVALID =
AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
AND S1.STATUSID = AIM.DELIVERY.STATUSID
AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID =
AIM.DELIVERY.ARCHIVERETRIEVALID
AND AIM.DELIVERY.DELIVERYMETHODID =
AIM.DELIVERYMETHOD.DELIVERYMETHODID
AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP)
OR (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND AIM.AIMCONNECTION.USERID = 'cfradle'
AND AIM.CHANNEL.CHANNEL =
(SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION,
AIM.AIMCONNECTION WHERE
AIM.CHANNELSESSION.CHANNELSESSIONID =
AIM.AIMCONNECTION.CHANNELSESSIONID
AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
AND AIM.AIMCONNECTION.AIMCONNECTIONID = 2)
GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
S1.STATUS,
AIM.DELIVERYMETHOD.DELIVERYMETHOD,
AIM.DELIVERY.DELIVERYADDRESS,
AIM.DELIVERY.DELIVERYDATE,
AIM.DELIVERY.VALIDATIONSTATUSID
ORDER BY AIMRETRIEVALID
FOR FETCH ONLY WITH UR;
I'm surprised you're not getting an error referring to the extreme size of
the query or an excessive number of tables being referenced; that may be

the longest query I've ever seen and I've seen (and written) some big ones ;-)

It's a little hard to digest this monster but one thing caught my eye: does the AIM.STATUS table really have a column called STATUS? I don't often see
tables whose column names match the table names. When you use the column
name S1.STATUS do you perhaps mean S1.SOME_OTHER_COLUMN_NAME? Remember,
S1.STATUS will resolve to AIM.STATUS.STATUS where AIM is the schema, the
first STATUS is the table name, and the second status is the column name.

Also, in the subquery that starts "SELECT CHANNEL FROM ....", is the CHANNEL column unique to one of the tables named in the subquery's FROM clause? If
not, that might be the source of the problem since the column name is
completely unqualified.

Something that might help make this query a bit more readable is to use
table name substitutions more: just as you identify S1 as a reference to
AIM.STATUS, if you used other short references for the other tables in the
FROM clause, you could shorten the references to the column names
considerably throughout the rest of the query.

Something else that is confusing is that your main FROM clause appears to
name 9 tables (and comment 2 others) but I'm only seeing joining conditions (ON clauses) being expressed for AIM.ARCHIVERETRIEVAL and AIM.DELIVERY.
[AIM.ARCHIVERETRIEVAL seems to be getting outer joined to AIM.DELIVERY, then to AIM.DELIVERY, on a different condition, again. Is that what you really
mean to do?] If I'm not mistaken, that means that you are using the "old
syntax" to do implicit natural joins on all the other pairs of tables and
putting their joins in the WHERE clause. As a point of style, I prefer to
state all of my joins explicitly using INNER JOIN or (LEFT/RIGHT/FULL) OUTER JOIN and putting all of my join conditions in ON clauses within the FROM
clause, then reserving the WHERE clause for local predicates (predicates
that affect only a single table) alone. I find it easier to read queries
that way. If you were to try this, your problem might become more obvious to you (and others).

Another technique I've found useful for debugging big queries is to comment out sections that seem suspect and see if the problem goes away; that method helps you find the area where the problem lies and gradually isolate the
error. For instance, if you commented half the tables in the FROM clause and their joins, if the problem went away, you could reasonably assume that the commented lines contained the ambiguous reference. It's not necessarily a
very quick way to find a problem but it might be faster than having to wait for someone to reply to a newsgroup post.

Rhino

[Rhino]

"Toke" <no****@donttryspammingme.com> wrote in message
news:41*********************@dread11.news.tele.dk. ..

I have had problems with DB2 on iSeries. My conclusion was that FULL OUTER
JOIN isn't possible on DB2 for iSeries.

I don't have iSeries so I can't prove or disprove that but...

I'd be *very* surprised if that were true. IBM does a *lot* of testing on
DB2 before releasing it. They don't catch everything, as we can see from the
bug fixes in the fixpacks, but I'd be surprised if they missed something as
basic as FULL OUTER JOINs not working.

If you can make a case that FULL OUTER JOINs don't work on iSeries machines,
I imagine that IBM would love to hear from you so that they could address
the problem for all iSeries users.

Rhino

[Toke]

Could be that I'm running an old version of DB2 on iSeries. It won't work.
Works fine if i replace "FULL OUTER JOIN" with "LEFT JOIN". But of course it
gives the wrong result.

I'm running OS/400 ver. 5,3
"Rhino" <rh****@NOSPAM.sympatico.ca> skrev i en meddelelse
news:wA*********************@news20.bellglobal.com ...

"Toke" <no****@donttryspammingme.com> wrote in message
news:41*********************@dread11.news.tele.dk. ..

I have had problems with DB2 on iSeries. My conclusion was that FULL OUTER JOIN isn't possible on DB2 for iSeries.
I don't have iSeries so I can't prove or disprove that but...

I'd be *very* surprised if that were true. IBM does a *lot* of testing on
DB2 before releasing it. They don't catch everything, as we can see from

the bug fixes in the fixpacks, but I'd be surprised if they missed something as basic as FULL OUTER JOINs not working.

If you can make a case that FULL OUTER JOINs don't work on iSeries machines, I imagine that IBM would love to hear from you so that they could address
the problem for all iSeries users.

Rhino