Solved

oracle query

Posted on 2014-02-07
20
229 Views
Last Modified: 2014-02-21
select distinct d.coll_date,d.unit_id,dn.donor_id,
       first_name,last_name,procedure_code,inter_code,
         mc.description as blood_type      
  from donations_don d,
       donors_don dn,
         master_codes mc,
         donor_interdictions_don di
 where d.donor_id = dn.donor_id
   and di.donor_id = dn.donor_id
   and dn.blood_type = mc.udf1
   and procedure_code = 'WB'
   and mc.code_type = 'ABO'
   and di.inter_code != 'HLAP'
   and d.coll_date between '01-feb-2014' and '06-feb-2014'
   and d.unit_id is not null
   and gender = 'F'
   and blood_type in (28,84)


Janice sigona - Has Inter_code of PRG3X and Donna and Tony do not have that inter code but have different. My requirement id show all donors but if they have inter_code like PRG% then display other wise do not display any intercode but only display donor once not multiple lines like I have it. I tried Case statement but still did not get.
data.JPG
0
Comment
Question by:anumoses
  • 11
  • 5
  • 4
20 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39842392
please post data as text.  We can't use pictures.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39842400
the picture shows the wrong results?

what would the correct results look like  - please post text rows and columns, not a word description
0
 
LVL 6

Author Comment

by:anumoses
ID: 39842420
posted excel
data.xls
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39842429
what is that data?

what you want or what you have?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39842441
Same donor is displayed multiple times because of the inter_code. What I need is If inter_code like 'PRG%' then display else do not display any inter_code. But I need all the distinct donors for that date range.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39842485
so, that xls is a file full of wrong results?

please post an example of what you DO want
0
 
LVL 6

Author Comment

by:anumoses
ID: 39842489
case when inter_code like ('PRG%') then inter_code else null end inter_code

If I use case I get DN00462184 Janice Sigona twice as she has two interdictions. But I want to display only that line that has PRG%
0
 
LVL 6

Author Comment

by:anumoses
ID: 39842492
I should get 25 distinct donors. For these 25 donors only two donors have inter_code of PRG% . Rest should be null values.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 250 total points
ID: 39842787
Modify your query to include the case statement, then
select <column_names> from
(select <column_names>,
 row_number() over (partition by <column_names other than inter_code>
                                  order by inter_code) rn
 from
 <yourmodifiedquery>)
)
where rn = 1;
0
 
LVL 6

Author Comment

by:anumoses
ID: 39842812
Your query gives me Janice Sigona without an inter_code but she has PRG% inter_code
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 32

Expert Comment

by:awking00
ID: 39842830
In my test it returned 25 distinct donors with only Janice Sigona and Julie Wood having an inter_code of PTG% and the rest null. What is the precise query you tried?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39842835
sorry for the typo, PRG not PTG.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39842842
select coll_date,unit_id,donor_id,first_name,last_name,
       procedure_code,inter_code,blood_type
from(
select distinct d.coll_date,d.unit_id,dn.donor_id,
       first_name,last_name,procedure_code,
         case when inter_code like ('PRG%') then inter_code end inter_code,
         mc.description as blood_type,
       row_number() over (partition by d.coll_date,d.unit_id,dn.donor_id,first_name,
                                       last_name,procedure_code,blood_type
                                  order by inter_code) rn      
  from donations_don d,
       donors_don dn,
         master_codes mc,
         donor_interdictions_don di
 where d.donor_id = dn.donor_id
   and di.donor_id = dn.donor_id
   and dn.blood_type = mc.udf1
   and procedure_code = 'WB'
   and mc.code_type = 'ABO'
   and di.inter_code != 'HLAP'
   and d.coll_date between '01-feb-2014' and '06-feb-2014'
   and d.unit_id is not null
   and gender = 'F'
   and blood_type in (28,84)
  )
where rn = 1;
0
 
LVL 32

Expert Comment

by:awking00
ID: 39842862
You left out the distinct keyword from your original query. The query should show 26 rows with only Janice Sigona having two rows one with an inter_code of PRG3X and one with an inter_code of null. There should be one row for each of the other donors with only Julie Wood having a non-null inter_code value. What does your query show?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39842875
When I run the query I get only one Julie Wood having PRG1X but Janice its null even after modifying the query
0
 
LVL 32

Expert Comment

by:awking00
ID: 39847212
What do the following queries produce?
select inter_code
from donor_interdictions_don
where upper(first_name) = 'JANICE'
    and upper(last_name) = 'SIGONA';

select inter_code, count(*) from
(select case when inter_code like 'PRG%' then inter_code else null end as inter_code
 from donor_interdictions_don
 where upper(first_name) = 'JANICE'
     and upper(last_name) = 'SIGONA')
group by inter_code;
0
 
LVL 6

Author Comment

by:anumoses
ID: 39847334
Donor_interdictions_don has no name. Only Donors_don table has that info.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39847346
select distinct inter_code
from donors_don a,donor_interdictions_don b
where a.donor_id = b.donor_id
  and upper(first_name) = 'JANICE'
    and upper(last_name) = 'SIGONA';

INTER_CODE

CMVP
DDT
DRDEF
F10L
F5X
F6XL
F9XL
FS04
HEMOG
HLAP
L615
LCMP
NOCON
NOPHP
NOREC
PRG3X
UNTIL
ZPHLA


select inter_code, count(*) from
(select case when inter_code like 'PRG%' then inter_code else null end as inter_code
 from donors_don a, donor_interdictions_don b
 where a.donor_id = b.donor_id
   and upper(first_name) = 'JANICE'
     and upper(last_name) = 'SIGONA')
group by inter_code;

INTER_CODE,COUNT(*)
PRG3X,1
,41
0
 
LVL 6

Author Comment

by:anumoses
ID: 39847350
select inter_code
from donors_don a,donor_interdictions_don b
where a.donor_id = b.donor_id
  and upper(first_name) = 'JANICE'
    and upper(last_name) = 'SIGONA';

INTER_CODE
CMVP
DDT
DDT
DDT
DDT
DDT
DDT
DDT
DDT
DDT
DRDEF
DRDEF
DRDEF
DRDEF
DRDEF
DRDEF
DRDEF
DRDEF
DRDEF
F10L
F5X
F6XL
F9XL
FS04
HEMOG
HEMOG
HEMOG
HEMOG
HEMOG
HEMOG
HEMOG
HEMOG
HEMOG
HLAP
L615
LCMP
NOCON
NOPHP
NOREC
PRG3X
UNTIL
ZPHLA
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39876708
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parametric query in oracle 6 49
System.InvalidCastException: Specified cast is not valid 10 67
Oracle query output question 4 36
sort a spool into file output in oracle 1 21
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…
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 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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

948 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

19 Experts available now in Live!

Get 1:1 Help Now