Harry wrote:
> Maxim Demenko wrote...
>
>
>>Harry wrote:
>
>
>>>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;
>
>
> 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/metalink/plsql/ml2_documents.showDocument?p_database_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(+))
Best regards
Maxim
Received on Fri Jul 22 2005 - 16:41:50 CDT
