Simple Query - but answer eludes me

[Anthony Robinson]

Hopefully I can explain what I'm trying to do well enough for someone
to be able to shed some light on my problem.

I have three tables: AIMRETRIEVAL
-->ARCHIVERETRIEVAL-->AIMRETRIEVEDITEM. There is a one-to-many
relationship between AIMRETRIEVAL and ARCHIVERETRIEVAL. The foreign
key on ARCHIVERETRIEVAL is AIMRETRIEVALID. There is also a one-to-many
relationship between ARCHIVERETRIEVAL and AIMRETRIEVEDITEM. The
foreign key on AIMRETRIEVEDITEM is ARCHIVERETRIEVALID.

There is a field in AIMRETRIEVEDITEM called IMAGEFRONT - holds a CLOB.

I would like to 1.) SELECT all AIMRETRIEVALS based on a userid.
2.) SELECT all ARCHIVERETRIEVALS associated with each AIMRETRIEVAL.
3.) For each ARCHIVERETRIEVAL, get totalt count of how many images
there are, then check to see how many AIMRETRIEVEDITEM's are
available (IMAGEFRONT IS NOT NULL).

Here is what I've tried so far. I thought the union would work, but
not getting back the right counts:

SELECT
DISTINCT 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 -
COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) AS
NUMBERIMAGESAVAILABLE
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE
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.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NOT NULL
AND AIM.AIMCONNECTION.USERID = strUserID
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
UNION
SELECT
DISTINCT 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 -
COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) AS
NUMBERIMAGESAVAILABLE
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE
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.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL
AND AIM.AIMCONNECTION.USERID = strUserID
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;
OPEN SELECT_CURSOR;

I've also tried this:

CREATE PROCEDURE AIM.GetUserIDRetrievals (IN strUserID VARCHAR(64) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
LANGUAGE SQL
RESULT SETS 1
P1: BEGIN
DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
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,
AIM.ARCHIVERETRIEVAL.ITEMCOUNT -
COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) AS
NUMBEROFIMAGESAVAILABLE
FROM
AIM.AIMCONNECTION,
AIM.AIMQUERY,
AIM.AIMRETRIEVAL,
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM,
AIM.ARCHIVETYPE
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.ARCHIVETYPE.ARCHIVETYPE = 'D'
AND (LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL OR
LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NOT NULL)
AND AIM.AIMCONNECTION.USERID = strUserID
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;

Hopefully I've explained this well enough, but haven't droned on too
long.

Any advice would be greatly appreciated.

Thanks in adavance!

AMR