Other Oracle database topics - please help with a query

Author

please help with a query

Harry

2005-07-22, 8:23 pm

I have two oracle 9i tables on Win2003 server.
For simplicity, let say:
Table A contains two fields: A.id, A.part_id.
Table B contains two fields: B.part_id, B.desc.

When I ran the following sqlplus query,

select
A.id, B.desc
from
A, B
where
A.part_id = B.part_id
and A.part_id <= 20;

I got some entries returned. So far so good.
E.G.
id Desc
---- ----
5 head
10 chest

What I want is an sql which will return the following results.

id Desc
--- ----
5 head
10 chest
15 <empty> <-- when no B.part_id corresponds to A.part_id
20 <empty> '' ''

How should I formulate such an query?

TIA

Maxim Demenko

2005-07-22, 8:23 pm

Harry wrote:
> I have two oracle 9i tables on Win2003 server.
> For simplicity, let say:
> Table A contains two fields: A.id, A.part_id.
> Table B contains two fields: B.part_id, B.desc.
>
> When I ran the following sqlplus query,
>
> select
> A.id, B.desc
> from
> A, B
> where
> A.part_id = B.part_id
> and A.part_id <= 20;
>
> I got some entries returned. So far so good.
> E.G.
> id Desc
> ---- ----
> 5 head
> 10 chest
>
> What I want is an sql which will return the following results.
>
> id Desc
> --- ----
> 5 head
> 10 chest
> 15 <empty> <-- when no B.part_id corresponds to A.part_id
> 20 <empty> '' ''
>
>
> How should I formulate such an query?
>
> TIA
>

Non ANSI:

select A.id,B.desc
from A,B
where A.part_id=B.part_id(+)
and A.part_id <= 20;

ANSI

select A.id,B.desc
from A left outer join B on (A.part_id=B.part_id)
where A.part_id<=20;

Best Regards

Maxim

Harry

2005-07-22, 8:23 pm

Maxim Demenko wrote...

>Harry wrote:

>
>Non ANSI:
>
>select A.id,B.desc
>from A,B
>where A.part_id=B.part_id(+)
>and A.part_id <= 20;
>
>ANSI
>
>select A.id,B.desc
>from A left outer join B on (A.part_id=B.part_id)
>where A.part_id<=20;

Thanks Maxim for your reply.

I still cannot make my query work.
Below is the real problem (instead of the simplied version above).

When I ran SQL#1, I got 7 rows returned -- OK.
When I ran SQL#2, I got 0 row returned -- expect 7 rows, with "Body Part"
column empty.

SQL#1 -- un-reported studies
-----

select distinct
concat(concat(p.my_last_name,', '),p.my_first_name) as "Patient Full Name",
cast(pp.my_public_id AS varchar2(20)) as "Patient ID",
to_char(s.my_exam_date_time, 'YYYY-MM-DD hh:mi:ss') as "Performed On",
concat(concat(RefPhy
.my_last_name,', '),RefPhy.my_first_name) as "Referring
Physician",
s.my_study_id as "Study ID",
s.my_accession_number as acc_num,
decode (s.my_status,
0, 'Scheduled',
1, 'InProgress',
2, 'Performed',
3, 'Reported',
4, 'Teaching',
5, 'Reviewed',
6, 'Needs Over Read',
7, 'Transcribed',
8, 'Dictated') as "Study Status",
m.my_name as Modality

from
study s,
series sr,
patient p,
patient_public_id pp,
person RefPhy,
modality m

where
sr.study_id = s.my_study_id
and s.patient_id = p.my_patient_id
and p.my_patient_id = pp.patient_id
and s. referring_physicain_
id = RefPhy.my_person_id
and s.my_status in (2, 5, 6)
and sr.modality_id = m.my_modality_id ;

SQL#2 -- un-reported studies with "body part" included
-----
select distinct
concat(concat(p.my_last_name,', '),p.my_first_name) as "Patient Full Name",
cast(pp.my_public_id AS varchar2(20)) as "Patient ID",
to_char(s.my_exam_date_time, 'YYYY-MM-DD hh:mi:ss') as "Performed On",
concat(concat(RefPhy
.my_last_name,', '),RefPhy.my_first_name) as "Referring
Physician",
s.my_study_id as "Study ID",
s.my_accession_number as acc_num,
decode (s.my_status,
0, 'Scheduled',
1, 'InProgress',
2, 'Performed',
3, 'Reported',
4, 'Teaching',
5, 'Reviewed',
6, 'Needs Over Read',
7, 'Transcribed',
8, 'Dictated') as "Study Status",
m.my_name as Modality,
bp.my_code as "Body Part"

from
study s,
series sr,
patient p,
patient_public_id pp,
person RefPhy,
modality m,
study_proc_type pr,
procedure_type prt,
procedure_body_part pbp,
body_part bp

where
sr.study_id = s.my_study_id
and s.patient_id = p.my_patient_id
and p.my_patient_id = pp.patient_id
and s. referring_physicain_
id = RefPhy.my_person_id
and s.my_status in (2, 5, 6)
and sr.modality_id = m.my_modality_id (+)

and s.my_study_id = pr.study_id (+)
and pr.procedure_type_id = prt. my_procedure_type_id
(+)
and prt. my_procedure_type_id
= pbp.procedure_type_id (+) <-- see N.B.
and pbp.body_part_id = bp.my_body_part_id ;

N.B. pbp.procedure_type_id has some values != prt. my_procedure_type_id

Maxim Demenko

2005-07-22, 8:23 pm

Harry wrote:
> Maxim Demenko wrote...
>
>
>
>
>
>
>
>
>
>
> Thanks Maxim for your reply.
>
> I still cannot make my query work.
> Below is the real problem (instead of the simplied version above).
>
> When I ran SQL#1, I got 7 rows returned -- OK.
> When I ran SQL#2, I got 0 row returned -- expect 7 rows, with "Body Part"
> column empty.
>
> SQL#1 -- un-reported studies
> -----
>
> select distinct
> concat(concat(p.my_last_name,', '),p.my_first_name) as "Patient Full Name",
> cast(pp.my_public_id AS varchar2(20)) as "Patient ID",
> to_char(s.my_exam_date_time, 'YYYY-MM-DD hh:mi:ss') as "Performed On",
> concat(concat(RefPhy
.my_last_name,', '),RefPhy.my_first_name) as "Referring
> Physician",
> s.my_study_id as "Study ID",
> s.my_accession_number as acc_num,
> decode (s.my_status,
> 0, 'Scheduled',
> 1, 'InProgress',
> 2, 'Performed',
> 3, 'Reported',
> 4, 'Teaching',
> 5, 'Reviewed',
> 6, 'Needs Over Read',
> 7, 'Transcribed',
> 8, 'Dictated') as "Study Status",
> m.my_name as Modality
>
> from
> study s,
> series sr,
> patient p,
> patient_public_id pp,
> person RefPhy,
> modality m
>
> where
> sr.study_id = s.my_study_id
> and s.patient_id = p.my_patient_id
> and p.my_patient_id = pp.patient_id
> and s. referring_physicain_
id = RefPhy.my_person_id
> and s.my_status in (2, 5, 6)
> and sr.modality_id = m.my_modality_id ;
>
> SQL#2 -- un-reported studies with "body part" included
> -----
> select distinct
> concat(concat(p.my_last_name,', '),p.my_first_name) as "Patient Full Name",
> cast(pp.my_public_id AS varchar2(20)) as "Patient ID",
> to_char(s.my_exam_date_time, 'YYYY-MM-DD hh:mi:ss') as "Performed On",
> concat(concat(RefPhy
.my_last_name,', '),RefPhy.my_first_name) as "Referring
> Physician",
> s.my_study_id as "Study ID",
> s.my_accession_number as acc_num,
> decode (s.my_status,
> 0, 'Scheduled',
> 1, 'InProgress',
> 2, 'Performed',
> 3, 'Reported',
> 4, 'Teaching',
> 5, 'Reviewed',
> 6, 'Needs Over Read',
> 7, 'Transcribed',
> 8, 'Dictated') as "Study Status",
> m.my_name as Modality,
> bp.my_code as "Body Part"
>
> from
> study s,
> series sr,
> patient p,
> patient_public_id pp,
> person RefPhy,
> modality m,
> study_proc_type pr,
> procedure_type prt,
> procedure_body_part pbp,
> body_part bp
>
> where
> sr.study_id = s.my_study_id
> and s.patient_id = p.my_patient_id
> and p.my_patient_id = pp.patient_id
> and s. referring_physicain_
id = RefPhy.my_person_id
> and s.my_status in (2, 5, 6)
> and sr.modality_id = m.my_modality_id (+)
>
> and s.my_study_id = pr.study_id (+)
> and pr.procedure_type_id = prt. my_procedure_type_id
(+)
> and prt. my_procedure_type_id
= pbp.procedure_type_id (+) <-- see N.B.
> and pbp.body_part_id = bp.my_body_part_id ;
>
>
> N.B. pbp.procedure_type_id has some values != prt. my_procedure_type_id

>
>

Maybe the Note
https://metalink.oracle.com/metalin...T&p_id=116447.1
will be interesting for you, anyway, try to add (+) to your last
condition in where clause ( and bp.my_body_part_id = pbp.body_part_id(+))

Best regards

Maxim

Harry

2005-07-22, 8:23 pm

Maxim Demenko wrote...

>Maybe the Note
>https://metalink.oracle.com/metalin...ocument?p_datab
ase_id=NOT&p_id=116447.1
>will be interesting for you, anyway, try to add (+) to your last
>condition in where clause ( and bp.my_body_part_id = pbp.body_part_id(+))

Ahha!
That does it.

Many thanks

Sponsored Links