asked on

Oracle query

select distinct d.drive_id,d.unit_id||'-'||chk_digit as unit_id,
dn.donor_id,first_name,last_name,inter_code,
                   trunc(di.insert_date) as insert_date,
       mc.description as blood_type
from donations_don d,
donors_don dn,
       master_codes mc,
       units_lab u,
       donor_interdictions_don di
where d.donor_id = dn.donor_id
and di.donor_id = dn.donor_id
and d.unit_id = u.unit_id
and dn.blood_type = mc.udf1
and procedure_code = 'WB'
and mc.code_type = 'ABO'
and (di.inter_code in ('HLAP','HLAN')
or di.inter_code is null)
and di.term_date is null
and d.coll_date between '14-apr-2014' and '14-apr-2014'
and d.unit_id is not null
and gender = 'F'
and blood_type in (28,84)
order by 1,2

But I have a donor that is not showing up on the report. This donor is not tested and we do not have an entry in the interdictions table. But I also want to show that donor that the donor was not tested.

select donor_id,coll_date
from donations_don
where UNIT_ID = 'W039714879997'

DONOR_ID, COLL_DATE
DN20322530, 4/14/2014

select donor_id, inter_code, unit_id
from donor_interdictions_don
where donor_id = 'DN20322530'

No data found

But I need to show this donor on the report. I tried using the below condition but could not get the right results.

and (di.inter_code in ('HLAP','HLAN')
or di.inter_code is null
or not exists (select donor_id from donor_interdictions_lab don
ab-donors.xls

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Alex [***Alex140181***]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>Why not?!

Oracle didn't use to support the ANSI syntax. I forget what release they started allowing it.

anumoses

ASKER

I tried using this query. But I am not getting the one donor that was not tested.

select distinct d.transaction_id,d.drive_id,d.unit_id||'-'||chk_digit as unit_id,
dn.donor_id,first_name,last_name,null,
                   null,
       mc.description as blood_type
from donations_don d,
donors_don dn,
       master_codes mc,
       units_lab u
where d.donor_id = dn.donor_id
and d.unit_id = u.unit_id
and dn.blood_type = mc.udf1
and procedure_code = 'WB'
and mc.code_type = 'ABO'
and d.transaction_id not in (select di.transaction_id
             from donation_interdictions_don di
       where di.transaction_id = d.transaction_id)
and d.coll_date between '14-apr-2014' and '14-apr-2014'
and d.unit_id is not null
and gender = 'F'
and blood_type in (28,84)
and d.donor_id ='DN20322530'
order by 1,2

but when I run the below query

select distinct d.drive_id,d.unit_id||'-'||chk_digit as unit_id,
dn.donor_id,first_name,last_name,null,
                   null,
       mc.description as blood_type
from donations_don d,
donors_don dn,
       master_codes mc,
       units_lab u
where d.donor_id = dn.donor_id
and d.unit_id = u.unit_id
and dn.blood_type = mc.udf1
and procedure_code = 'WB'
and mc.code_type = 'ABO'
and d.coll_date between '14-apr-2014' and '14-apr-2014'
and d.unit_id is not null
and gender = 'F'
and blood_type in (28,84)
and d.donor_id ='DN20322530'
order by 1,2

DRIVE_ID,UNIT_ID,DONOR_ID,FIRST_NAME,LAST_NAME,NULL,NULL_1,BLOOD_TYPE

DRV2023865,W039714879997-8,DN20322530,SELENA,PRYSOCK,,,AB Pos

This is the donor that was not tested

Sharath S

What is your Oracle version?

anumoses

ASKER

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

thanks