• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

oracle query

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
anumoses
Asked:
anumoses
  • 5
  • 4
1 Solution
 
DultonCommented:
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
 
anumosesAuthor Commented:
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
 
DultonCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
anumosesAuthor Commented:
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
 
DultonCommented:
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
 
anumosesAuthor Commented:
With DESC NULL LAST I am getting missing parenthesis error.
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
I am sorry. I am still on 9i or oracle
0
 
DultonCommented:
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
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now