Re: please help with a query

From: Harry <harryooopotter_at_hotmail.co_>
Date: Fri, 22 Jul 2005 21:09:49 GMT
Message-ID: <xgdEe.166746$on1.63193@clgrps13>

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 Received on Fri Jul 22 2005 - 16:09:49 CDT