anumoses
asked on
oracle query
select distinct d.coll_date,d.unit_id,dn.d onor_id,
first_name,last_name,proce dure_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
first_name,last_name,proce
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
please post data as text. We can't use pictures.
the picture shows the wrong results?
what would the correct results look like - please post text rows and columns, not a word description
what would the correct results look like - please post text rows and columns, not a word description
ASKER
posted excel
data.xls
data.xls
what is that data?
what you want or what you have?
what you want or what you have?
ASKER
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.
so, that xls is a file full of wrong results?
please post an example of what you DO want
please post an example of what you DO want
ASKER
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%
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%
ASKER
I should get 25 distinct donors. For these 25 donors only two donors have inter_code of PRG% . Rest should be null values.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your query gives me Janice Sigona without an inter_code but she has PRG% inter_code
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?
sorry for the typo, PRG not PTG.
ASKER
select coll_date,unit_id,donor_id ,first_nam e,last_nam e,
procedure_code,inter_code, blood_type
from(
select distinct d.coll_date,d.unit_id,dn.d onor_id,
first_name,last_name,proce dure_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.d onor_id,fi rst_name,
last_name,procedure_code,b lood_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;
procedure_code,inter_code,
from(
select distinct d.coll_date,d.unit_id,dn.d
first_name,last_name,proce
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.d
last_name,procedure_code,b
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;
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?
ASKER
When I run the query I get only one Julie Wood having PRG1X but Janice its null even after modifying the query
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;
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;
ASKER
Donor_interdictions_don has no name. Only Donors_don table has that info.
ASKER
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
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
ASKER
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
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
ASKER
thanks