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;