OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » NOT IN

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NOT IN [message #185627]

Wed, 02 August 2006 14:33

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

I have a query that outputs a complete diet census. It works perfectly...Here is the SQL

SELECT /*pulls diets based on greatest sub_seq number (usually latest diet)  This query pulls STAR diets*/    
   res1.patient_name Patient_Name,
   res1.patient_id,
   res1.dept_id,
   res1.room_id,
   res1.bed_id
FROM
   (SELECT
      p.last_name|| ',' || p.first_name|| ' ' || p.middle_name Patient_name,
      p.patient_id,
      p.dept_id,
      p.room_id,
      p.bed_id,
      p.birth_dt,
      op.sub_seq,
      op.dc_ddt,
      MAX(op.sub_seq)
   OVER 
   (PARTITION BY 
      p.last_name|| ',' || p.first_name|| ' ' || p.middle_name,
      p.patient_id) max_sub_seq
   FROM
      patient p,
      o_pat op,
      o_pat_occur opo,
      o_pat_resp opr,
      o_item oi,
      ccdata.facility f,
      cpi
   WHERE  
          p.pat_seq  = op.pat_seq(+)
      AND op.order_seq = opo.order_seq(+)
      AND op.order_seq = opr.order_seq(+)
      AND p.cpi_seq = cpi.cpi_seq
      AND op.order_item_seq = oi.order_item_seq
      AND p.facility_id IN ('P')
      AND f.facility_sname IN ('PWMC')
      AND oi.perf_dept_id IN ('NOU','DTP','NOP','DTB','NOB','DTF','NOF','DTS','NOS','DTL','NOL')
      AND p.dept_id != 'TRNG'
      AND p.discharge_dt IS NULL
      AND op.frequency_id = 'MEALS'
      AND DDT.TODATE(op.start_ddt) < (SYSDATE + (((.01)*60)/1440))/* TO SEE DIETS THAT ARE EITHER ACTIVE OR WILL BE WITHIN THE NEXT MINUTE */) res1
WHERE
       res1.sub_seq = res1.max_sub_seq
   AND res1.dc_ddt IS NULL
UNION
SELECT /*pulls diets based on active status, and dc_ddt is null.  If diet has future dc_ddt the above query will catch*/
   res2.patient_name Patient_Name,
   res2.patient_id,
   res2.dept_id,
   res2.room_id,
   res2.bed_id
FROM
   (SELECT
      p2.last_name|| ',' || p2.first_name|| ' ' || p2.middle_name Patient_name,
      p2.patient_id,
      p2.dept_id,
      p2.room_id,
      p2.bed_id,
      p2.birth_dt
   FROM
      patient p2,
      o_pat op2,
      o_pat_resp opr2,
      o_pat_occur opo2,
      o_item oi2,
      ccdata.facility f2,
      cpi cpi2
   WHERE  
               p2.pat_seq = op2.pat_seq(+)
      AND op2.order_seq = opo2.order_seq(+)
      AND op2.order_seq = opr2.order_seq(+)
      AND p2.cpi_seq = cpi2.cpi_seq 
      AND op2.order_item_seq = oi2.order_item_seq
      AND f2.facility_id = p2.facility_id 
      AND p2.facility_id IN ('P')
      AND f2.facility_sname IN ('PWMC')
      AND oi2.perf_dept_id IN ('NOU','DTP','NOP','DTB','NOB','DTF','NOF','DTS','NOS','DTL','NOL')
      AND op2.status IN ('A','F')
      AND ((DDT.TODATE(op2.dc_ddt) IS NULL)
       OR  (DDT.TODATE(op2.dc_ddt) > sysdate))
      AND p2.discharge_dt IS NULL  
      AND p2.dept_id != 'TRNG'
      AND op2.frequency_id = 'MEALS'
      AND TRUNC(DDT.TODATE(opo2.sched_ddt)) IN TRUNC(SYSDATE)
      AND ddt.todate(op2.start_ddt) < (SYSDATE + (((.01)*60)/1440))/* TO SEE DIETS THAT ARE EITHER ACTIVE OR WILL BE WITHIN THE NEXT MINUTE */) res2

Now I'm wanting to query all who either don't have an active diet or have never had a diet order and thought I could accomplish this through a NOT IN. Here is the SQL

select
   patient.patient_id
from
   patient
where
   patient.patient_id not in
   (select
      res3.patient_id
   from
(SELECT /*pulls diets based on greatest sub_seq number (usually latest diet)  This query pulls STAR diets*/    
   res1.patient_name Patient_Name,
   res1.patient_id,
   res1.birth_dt,
   res1.dept_id,
   res1.room_id,
   res1.bed_id

FROM
   (SELECT
      p.last_name|| ',' || p.first_name|| ' ' || p.middle_name Patient_name,
      p.patient_id,
      p.dept_id,
      p.room_id,
      p.bed_id,
      p.birth_dt,
      op.dc_ddt,
      op.sub_seq,
      DDT.TODATE(opo.sched_ddt) AS sdate,
      MAX(op.sub_seq)
   OVER 
   (PARTITION BY 
      p.last_name|| ',' || p.first_name|| ' ' || p.middle_name,
      p.patient_id) max_sub_seq
   FROM
      patient p,
      o_pat op,
      o_pat_occur opo,
      o_pat_resp opr,
      o_item oi,
      ccdata.facility f,
      cpi
   WHERE  
          p.pat_seq  = op.pat_seq(+)
      AND op.order_seq = opo.order_seq(+)
      AND op.order_seq = opr.order_seq(+)
      AND p.cpi_seq = cpi.cpi_seq
      AND op.order_item_seq = oi.order_item_seq
      AND p.facility_id IN ('P')
      AND f.facility_sname IN ('PWMC')
      AND oi.perf_dept_id IN ('NOU','DTP','NOP','DTB','NOB','DTF','NOF','DTS','NOS','DTL','NOL')
      AND p.dept_id != 'TRNG'
      AND p.discharge_dt IS NULL
      AND op.frequency_id = 'MEALS'
      AND DDT.TODATE(op.start_ddt) < (SYSDATE + (((.01)*60)/1440))/* TO SEE DIETS THAT ARE EITHER ACTIVE OR WILL BE WITHIN THE NEXT MINUTE */) res1
WHERE
       res1.sub_seq = res1.max_sub_seq
   AND res1.dc_ddt IS NULL

UNION

SELECT /*pulls diets based on active status, and dc_ddt is null.  If diet has future dc_ddt the above query will catch*/
   res2.patient_name Patient_Name,
   res2.patient_id,
   res2.birth_dt,
   res2.dept_id,
   res2.room_id,
   res2.bed_id
FROM
   (SELECT
      p2.last_name|| ',' || p2.first_name|| ' ' || p2.middle_name Patient_name,
      p2.patient_id,
      p2.dept_id,
      p2.room_id,
      p2.bed_id,
      p2.birth_dt,
      op2.dc_ddt,
      op2.sub_seq,
      DDT.TODATE(opo2.sched_ddt) AS sdate,
      op2.sub_seq
   FROM
      patient p2,
      o_pat op2,
      o_pat_resp opr2,
      o_pat_occur opo2,
      o_item oi2,
      ccdata.facility f2,
      cpi cpi2
   WHERE  
               p2.pat_seq = op2.pat_seq(+)
      AND op2.order_seq = opo2.order_seq(+)
      AND op2.order_seq = opr2.order_seq(+)
      AND p2.cpi_seq = cpi2.cpi_seq 
      AND op2.order_item_seq = oi2.order_item_seq
      AND f2.facility_id = p2.facility_id 
      AND p2.facility_id IN ('P')
      AND f2.facility_sname IN ('PWMC')
      AND oi2.perf_dept_id IN ('NOU','DTP','NOP','DTB','NOB','DTF','NOF','DTS','NOS','DTL','NOL')
      AND op2.status IN ('A','F')
      AND ((DDT.TODATE(op2.dc_ddt) IS NULL)
       OR  (DDT.TODATE(op2.dc_ddt) > sysdate))
      AND p2.discharge_dt IS NULL  
      AND p2.dept_id != 'TRNG'
      AND op2.frequency_id = 'MEALS'
      AND TRUNC(DDT.TODATE(opo2.sched_ddt)) IN TRUNC(SYSDATE)
      AND ddt.todate(op2.start_ddt) < (SYSDATE + (((.01)*60)/1440))/* TO SEE DIETS THAT ARE EITHER ACTIVE OR WILL BE WITHIN THE NEXT MINUTE */) res2)res3)

This query runs until I stop it. What am I doing wrong and any suggestions on how to go about this?

Thanks,
Stan

Report message to a moderator

Re: NOT IN [message #185690 is a reply to message #185627]

Thu, 03 August 2006 02:24

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

The query is too big, and there's FAR too little information provided for more than a cursory answer.

If you just want a list of patient ids and you know the query runs fairly quickly normaly, try

SELECT patient_id
FROM patient
MINUS
SELECT res3.patient_id
FROM
(SELECT /*pulls diets based on greatest sub_seq number (usually latest diet)  This query pulls STAR diets*/    
   res1.patient_name Patient_Name,...

Also, your queries have an outer join

p.pat_seq  = op.pat_seq(+)

which is rendered meaningless by then specifying a non-null value for columns in op later, eg

AND op.frequency_id = 'MEALS'

If your query is currently returning the correct rows, then get rid of that outer join, as it is just restricting the CBO when it comes to optimising the query.

Report message to a moderator

Re: NOT IN [message #185749 is a reply to message #185627]

Thu, 03 August 2006 07:19

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

Thanks for the advice...

I am searching for a null value in the op2 table:

      AND ((DDT.TODATE(op2.dc_ddt) IS NULL)
       OR  (DDT.TODATE(op2.dc_ddt) > sysdate))

So would this be meaningless?
I guess I should get rid of the

p.pat_seq  = op.pat_seq(+)

but keep

p2.pat_seq  = op2.pat_seq(+)

[Updated on: Thu, 03 August 2006 07:20]

Report message to a moderator

Re: NOT IN [message #185763 is a reply to message #185749]

Thu, 03 August 2006 08:02

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You should get rid of the OP2 outer join as well.
You've got this condition

AND op2.status IN ('A','F')

that requires a not nul value in part of the op2 record, so the outer join is unneccessary

Report message to a moderator

Re: NOT IN [message #185775 is a reply to message #185627]

Thu, 03 August 2006 09:48

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

Does it adversly affect my query by having the outer join?

Thanks
Stan

Report message to a moderator

Re: NOT IN [message #185891 is a reply to message #185775]

Fri, 04 August 2006 01:49

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Hard to say. The presence of outer joins does restrict the options that the CBO can consider for the query, and I believe they will run slightly slower, but there isn't a major performance impact UNLESS the presenceof the OJ stops the CBO picking a much better plan for the query.

Report message to a moderator

Re: NOT IN [message #186176 is a reply to message #185627]

Sun, 06 August 2006 18:29

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

Just curious....Why do you say the query is 'too big'? How could it be shortened? All of the conditions are needed for it to pull correct and accurate data.

Thanks,
Stan

Report message to a moderator

Re: NOT IN [message #186235 is a reply to message #186176]

Mon, 07 August 2006 02:40

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I'm sure it's fine.
What I mean is it's too big for me to be inclined to go through it line by line looking for problems.

In general, I'll look at any problem that comes with a set of scripts that let me reproduce it, or any problem that is clearly explained, or any problems that look 'interesting'.

Pasting 4.5 pages of sql running on an unknown data structure, with unknown indexs, data and statistics and saying 'It runs for too long' doesn't really inspire me to spend a great deal of time on the problem.

Report message to a moderator

Previous Topic:	Problems with Oracle9i...!
Next Topic:	How can I check this list of values are exists in database ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 17:33:24 CST 2024