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
Solved

oracle query

Posted on 2014-04-17
10
272 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
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
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
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 76

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

792 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