Greetings:

Having a little bit of trouble with a JON clause and would appreciate any help or advice anyone can give.

Here's the original query:

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
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE,
AIM.CHANNEL
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 (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 = strUserID
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 = decAIMConnectionID)
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,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT
ORDER BY AIMRETRIEVALID
FOR FETCH ONLY WITH UR;

This query returns 7 rows. Now, I've added a new table to the relationship, DELIVERY, and need to join on two columns in that table: STATUSID and DELIVERYMETHODID. Here's the issue: old data in the parent table (ARCHIVERETRIEVAL) doesn't have a corresponding row in the child table - DELIVERY. When joining, those old rows don't get retrieved.

So I added a couple of FULL OUTER JOINS. I want to retrieve the rows that match the other criteria, even if those rows do not have a corresponding row in DELIVERY. Here's my new queery:

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 AS S1 FULL OUTER JOIN AIM.DELIVERY AS DELIVERY ON S1.STATUSID = DELIVERY.STATUSID
FULL OUTER JOIN AIM.DELIVERYMETHOD AS DELIVERYMETHOD ON DELIVERY.DELIVERYMETHODID = 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 = strUserID
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 = decAIMConnectionID)
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;

This query only returns one row - the row that hjas a corresponding entry in DELIVERY.

I hope all this made any kind of sense!!

Thanks!

Use LEFT OUTER JOIN. It will include all rows in the Left table and for those rows in the Right table that do not match, null will be used for any columns in the result set that are derived from the Right table.

HTH

Andy