?
Solved

oracle query

Posted on 2014-04-17
10
Medium Priority
?
278 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 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