Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Simple / Complex Query - Counts

    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
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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.
    Attached Thumbnails Attached Thumbnails ResultsOfQuery.jpg  
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Andy:

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

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

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •