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

AMR,
Doesn't look too hard. I am going to skip the columns you want for simplicity sake.

SELECT a.*,b.*,(select count(*) from AIMRETRIEVEDITEM as c where (c.?? = b.??)) as quantity from AIMRETRIEVAL as a, ARCHIVERETRIEVAL as b
where (a.userID = xx) and (a.?? = b.??)

The ?? are the columns that are your foreign key relationships.

HTH

Andy

Here's the query I came up with:

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 LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
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 AIM.AIMCONNECTION.USERID = 'USER'
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;

And attached are the results:

I'm close but as you can see, the NumberofImagesAvailable is just a bit off. For example, for the very first row, the number of images available should be zero, since for that ARCHIVERETRIEVALID in the AIMRETRIEVEDITEM table, the IMAGEFRONT is NULL for both of the items (ItemCount=2, number of null images=2).

I'm sure I'm missing something really simple...just can't put my finger on it.

I think your problem is with this clause:

(SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT - COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM AIM.AIMRETRIEVEDITEM WHERE LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)

You did not connect it to the ARCHIVERETRIEVAL table. You are counting all of them regardless of the ARCHIVERETRIEVAL foreign key. You need to add to the where clause:

(SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT - COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM AIM.AIMRETRIEVEDITEM WHERE
(AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)

(I am not sure of the FK column anmes).

HTH

Andy

Andy:

You're some kind of a miracle worker...!!!

Thank you very much...owe you one (or two).