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(at)q8(dot)nrnet(dot)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