Solved

oracle query

Posted on 2014-04-17
10
276 Views
Last Modified: 2014-04-30
http://www.experts-exchange.com/Database/Oracle/Q_28413076.html

I had a query earlier that needed help. I tried to get a work around but have some duplicate lines. Need help.

select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
      di.inter_code,
      trunc(di.insert_date) date_of_test
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u,
       donor_interdictions@pdon_new di
where d.donor_id = dn.donor_id
and di.donor_id = d.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
and (di.inter_code in ('HLAP','HLAN')
     or di.inter_code is null)
and di.term_date is null
union
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
                  null,
        null
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u
where d.donor_id = dn.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
group by d.donor_id,first_name,last_name,dn.procedure_code,dn.unit_id,chk_digit,
dn.coll_date


If we take a look at the data file

DN00030842      SHERRIE      ROBOTHAM      PP      W039714880312-P      HLAN      2/28/2014
DN00030842      SHERRIE      ROBOTHAM      PP      W039714880312-P            
DN00071859      JEANNE      PIRON      PP      W039714867908-W            

DN00030842 only this line has to appear once since it was tested.
DN00071859 this  has to appear since this was not tested
data.xls
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 40006918
the basic methodology here is that you don't want to see any of the rows for that donor_id and unit_id where there is an existing date_of_test.... to do this, we need this code block twice, and a not exists comparing them.

It's been too many years since I lived in the oracle world. so I don't want to suggest temp tables or table variables because I'm not sure of their lifecycle. (or the context of your code). If you're comfortable with them, I'd recommend the first option because it'll eliminate processing this query twice. I'd load what you have to a temp table/table var and then:

select * from MyTempTable as mtt
where not exists (select mtt2.* from MyTempTable AS mtt2 where mtt.donor_Id = mtt2.donor_Id and mtt.unit_Id =mtt2.unit_id and mtt2.date_of_test is not null)

Open in new window


Option 2.
without using any temp tables/table vars:

SELECT t1.*
FROM
(select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
      di.inter_code,
      trunc(di.insert_date) date_of_test
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u,
       donor_interdictions@pdon_new di
where d.donor_id = dn.donor_id
and di.donor_id = d.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
and (di.inter_code in ('HLAP','HLAN')
     or di.inter_code is null)
and di.term_date is null
union
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
                  null,
        null
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u
where d.donor_id = dn.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
group by d.donor_id,first_name,last_name,dn.procedure_code,dn.unit_id,chk_digit, 
dn.coll_date) AS t1
WHERE NOT EXISTS (
select t2.* FROM 
(select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
      di.inter_code,
      trunc(di.insert_date) date_of_test
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u,
       donor_interdictions@pdon_new di
where d.donor_id = dn.donor_id
and di.donor_id = d.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
and (di.inter_code in ('HLAP','HLAN')
     or di.inter_code is null)
and di.term_date is null
union
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
                  null,
        null
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u
where d.donor_id = dn.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
group by d.donor_id,first_name,last_name,dn.procedure_code,dn.unit_id,chk_digit, 
dn.coll_date) as t2
where t1.donor_id = t2.donor_id and t1.unit_id = t2.unit_id and t2.date_of_test is not null
)

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40006958
I get only 1 line of data for the above query

DONOR_ID,FIRST_NAME,LAST_NAME,PROC,UNIT_ID,INTER_CODE,DATE_OF_TEST

DN00071859,JEANNE,PIRON,PP,W039714867908-W,,
0
 
LVL 6

Expert Comment

by:Dulton
ID: 40007002
apologies. I misread the requirement. we'll create ordering based upon the date_of_test field descending. we'll partition this ordering by donor_id and unit_id. this way the numbering will be unique to those 2 fields (each time a new donor or unit is involved, the numbering starts over.
With the numbering desc on date field, we'll take the top 1 of each partition/group.

SELECT t2.*
FROM
(
SELECT t1.*, ROW_NUMBER() OVER(PARTITION BY d.donor_id, d.unit_id ORDER BY date_of_test DESC) AS [DonorRow]
FROM
(
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
      di.inter_code,
      trunc(di.insert_date) date_of_test
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u,
       donor_interdictions@pdon_new di
where d.donor_id = dn.donor_id
and di.donor_id = d.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
and (di.inter_code in ('HLAP','HLAN')
     or di.inter_code is null)
and di.term_date is null
union
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
                  null,
        null
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u
where d.donor_id = dn.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
group by d.donor_id,first_name,last_name,dn.procedure_code,dn.unit_id,chk_digit, 
dn.coll_date
) as t1) AS t2
WHERE t2.donorRow = 1

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:anumoses
ID: 40007018
We are getting closer but not the two columns inter_code and date_of_test are not displayed for the ones the excel sheet shows.
0
 
LVL 6

Expert Comment

by:Dulton
ID: 40007579
I was ordering by date_of_test descending. trouble was null were coming up first, which is why you see no data.

if we use DESC NULL LAST instead of just DESC, it'll put the nulls at the end of our ordering.
you can see about DESC NULL LAST HERE
http://docs.oracle.com/javadb/10.8.1.2/ref/rrefsqlj13658.html

Code updated:

SELECT t2.*
FROM
(
SELECT t1.*, ROW_NUMBER() OVER(PARTITION BY d.donor_id, d.unit_id ORDER BY date_of_test DESC NULL LAST) AS [DonorRow]
FROM
(
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
      di.inter_code,
      trunc(di.insert_date) date_of_test
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u,
       donor_interdictions@pdon_new di
where d.donor_id = dn.donor_id
and di.donor_id = d.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
and (di.inter_code in ('HLAP','HLAN')
     or di.inter_code is null)
and di.term_date is null
union
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
                  null,
        null
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u
where d.donor_id = dn.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
group by d.donor_id,first_name,last_name,dn.procedure_code,dn.unit_id,chk_digit, 
dn.coll_date
) as t1) AS t2
WHERE t2.donorRow = 1 

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40007602
With DESC NULL LAST I am getting missing parenthesis error.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40008051
Dulton,

anumoses is still using Oracle 9i. I'm pretty sure the windowing functions like ROW_NUMBER were introduced in 10g.

anumoses,

Please remember to provide your Oracle version so Experts not familiar with your setup will know how to answer based on your specific situation.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40008543
I am sorry. I am still on 9i or oracle
0
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 40008570
I just went and pulled my Oracle 9i complete reference off the bookshelf and on page 1136 row_number() is listed as being supported in 9i in the way we're using it here. I don't think we have any fear of it being unsupported. Plus, as you saw, it ran without error in posts ID: 40007002 and your report back in ID: 40007018.

It seems 9i doesn't like DESC NULL LAST.   Instead, a decode statement will identify the nulls in the date column. (Rather than try to substitute some arbitrary date value with NVL() we'll use this column before the date column in our row_number() order by statement.



SELECT t2.*
FROM
(
SELECT t1.*
, ROW_NUMBER() OVER(PARTITION BY d.donor_id, d.unit_id ORDER BY [DateNullTest], date_of_test DESC) AS [DonorRow]
FROM
(
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
      di.inter_code,
      trunc(di.insert_date) date_of_test
,DECODE(di.insert_date,NULL,1,0) AS [DateNullTest]
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u,
       donor_interdictions@pdon_new di
where d.donor_id = dn.donor_id
and di.donor_id = d.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
and (di.inter_code in ('HLAP','HLAN')
     or di.inter_code is null)
and di.term_date is null
union
select d.donor_id, first_name,last_name,
        dn.procedure_code proc,
        dn.unit_id||'-'||chk_digit as unit_id,
                  null,
        null
from donors@pdon_new d,
       donations@pdon_new dn,
       units@plab.world u
where d.donor_id = dn.donor_id
and u.unit_id = dn.unit_id
and dn.coll_date between '16-apr-2014' and '16-apr-2014'
and d.gender = 'F'
and dn.procedure_code in ('RP', 'PP',  'CO', 'RS', 'PL')
and dn.deferral_id is null
group by d.donor_id,first_name,last_name,dn.procedure_code,dn.unit_id,chk_digit, 
dn.coll_date
) as t1) AS t2
WHERE t2.donorRow = 1 

Open in new window

0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40032257
thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question