Solved

oracle query

Posted on 2014-04-17
10
267 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
  • 5
  • 4
10 Comments
 
LVL 6

Expert Comment

by:Dulton
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
Comment Utility
With DESC NULL LAST I am getting missing parenthesis error.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
I am sorry. I am still on 9i or oracle
0
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
Comment Utility
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
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now