mere millimetres away

Rich,
---------------------------------------------------------
I got the following to work fine (so I had to break it!):
---------------------------------------------------------
SELECT tr_id, tr_date
FROM crtrd1 ALIAS1
WHERE ALIAS1.tr_unit = 'SMA'
  AND (ALIAS1.tr_type = 'A' OR ALIAS1.tr_type = 'I')
  AND ALIAS1.tr_id NOT IN (
        SELECT tr_id
        FROM crtrd1 ALIAS2
        WHERE ALIAS2.tr_unit = 'SMA'
          AND ALIAS2.tr_id = ALIAS1.tr_id
          AND (ALIAS2.tr_type = 'T' OR ALIAS2.tr_type = 'O')
          AND ALIAS2.tr_date > ALIAS1.tr_date
  )
ORDER BY tr_date DESC;

----------------------------------------------------------------------
I need to include the patient names so I tried a join to another table
----------------------------------------------------------------------
    SELECT crtrd1.tr_id, tr_date, client_lname, client_fname
    FROM crtrd1 ALIAS1, svcrd1 ALIAS0
    WHERE ALIAS1.tr_unit = 'SMA'
    AND ALIAS1.tr_id = ALIAS0.tr_id
    AND (ALIAS1.tr_type = 'A' OR ALIAS1.tr_type = 'I')
    AND NOT EXISTS(
        SELECT crtrd1.tr_id
        FROM crtrd1 ALIAS2
        WHERE ALIAS2.tr_unit = 'SMA'
            AND ALIAS2.tr_id = ALIAS1.tr_id
            AND (ALIAS2.tr_type = 'T' OR ALIAS2.tr_type = 'O')
            AND ALIAS2.tr_date > ALIAS1.tr_date
  )
ORDER BY tr_date DESC;

This monstrosity would eventually hang my box after generating a
75M core dump.

-----------------------------------------------------
Next I tried a multi-nested subquery type thingmabob.
-----------------------------------------------------
SELECT tr_id, client_lname, client_fname
FROM svcrd1
WHERE tr_id IN
  (SELECT tr_id, tr_date
  FROM crtrd1 ALIAS1
  WHERE ALIAS1.tr_unit = 'SMA'
  AND (ALIAS1.tr_type = 'A' OR ALIAS1.tr_type = 'I')
  AND NOT EXISTS(
        SELECT tr_id
        FROM crtrd1 ALIAS2
        WHERE ALIAS2.tr_unit = 'SMA'
          AND ALIAS2.tr_id = ALIAS1.tr_id
          AND (ALIAS2.tr_type = 'T' OR ALIAS2.tr_type = 'O')
          AND ALIAS2.tr_date > ALIAS1.tr_date
  ));

This buffoon does not generate stderr, does not create a core dump, etc.
It simply sits there grinning at me.

--------------------------------------------------------------------
So, I created a View (to a kill):
--------------------------------------------------------------------

CREATE VIEW placement
AS
SELECT crtrd1.tr_id, tr_date, tr_unit, tr_type,
       client_lname, client_fname, eth_nic
FROM crtrd1, svcrd1
WHERE crtrd1.tr_id = svcrd1.tr_id;

---------------------------------------------------------------------
This does work!  But when I tried this:
---------------------------------------------------------------------

select tr_id, tr_date, tr_unit, tr_type, client_lname, client_fname, eth_nic
from placement p
where p.tr_unit = 'SMA'
and (p.tr_type = 'A' or p.tr_type = 'I')
and not exists
     (select tr_id
      from placement p2
      where p2.tr_unit = 'SMA'
      and p.tr_id = p2.tr_id
      and (p2.tr_type = 'T' or p2.tr_type = 'O')
      and p2.tr_date > p.tr_date )
order by tr_date desc;


I get back 76 rows - i.e., it is not deleting the discharged pts,
so, the nested subquery is not happening.  If I change the NOT EXISTS
to NOT IN (removing the tr_id qualifier) I get back 0 rows.

If I split this subquery in half, both parts work!
How close do I gotta get before I see what's wrong here?!!!

Any advice greatly appreciated, as always!
Thanks,
Tom

    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056