pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmere millimetres away
2+ messages / 2 participants
[nested] [flat]
* mere millimetres away
@ 1998-08-05 20:15 Thomas Good <tomg@admin.nrnet.org>
0 siblings, 0 replies; 2+ messages in thread
From: Thomas Good @ 1998-08-05 20:15 UTC (permalink / raw)
To: Richard Lynch <lynch@lscorp.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: mere millimetres away
@ 1998-08-05 22:47 Richard Lynch <lynch@lscorp.com>
0 siblings, 0 replies; 2+ messages in thread
From: lynch@lscorp.com @ 1998-08-05 22:47 UTC (permalink / raw)
To: Thomas Good <tomg@admin.nrnet.org>; +Cc: pgsql-sql
At 4:15 PM 8/5/98, Thomas Good wrote:
>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;
You should try doing analyze on these things to see why they are taking so
long... I don't see anything wrong with this one, but I'm *NOT* an SQL
expert.
It may be trying to create a table that's just too large...
--
--
-- "TANSTAAFL" Rich lynch@lscorp.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1998-08-05 22:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-05 20:15 mere millimetres away Thomas Good <tomg@admin.nrnet.org>
1998-08-05 22:47 Re: mere millimetres away Richard Lynch <lynch@lscorp.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox