0 Replies Latest reply: Feb 9, 2010 10:14 AM by 751767 RSS

    Usage of Multiple Join Statements

    751767
      Hi Chaitanya,

      Please see below the query in which multiple outerjoins are used in same SQL Statements.


      select
      to_number(Null) dcm_id, to_number(Null) dcm_que_dcm_subset_sn,
      &tid1 template_id, &ktid1 key_template_id, &name1 view_name,
      &sname1 sas_ds_name, tc.display_sequence_no, tc.name oracle_name,
      tc.sas_name, tc.sas_label, 'ORACLE_VARIABLE' attribute_name,
      to_char(Null) pages, to_number(Null) pages_num,
      decode(em.data_type_code,'CHAR',em.data_type_code ||
      '(' || em.length || ')',em.data_type_code) data_type_length,
      to_char(null) dvg, em.sas_format sas_format
      from template_columns tc, extract_macros em
      where tc.template_id = &ktid1
      and tc.key_extract_macro_id = em.extract_macro_id
      UNION
      select
      dq.dcm_id, dq.dcm_que_dcm_subset_sn,
      &tid1 template_id, &ktid1 key_template_id, &name1 view_name,
      &sname1 sas_ds_name, 100+tc.display_sequence_no display_sequence_no,
      tc.name oracle_name, tc.sas_name, tc.sas_label, tc.attribute_name,
      decode(substr(i.name,1,4),'PAGE',to_char(to_number(substr(i.name,5))),
      i.name) pages,
      to_number(decode(substr(i.name,1,4),'PAGE',substr(i.name,5),
      9999)) pages_num,
      decode(dq.question_data_type_code,'CHAR',dq.question_data_type_code ||
      '(' || dq.length || ')','DATE','CHAR(' || dq.length || ')',
      dq.question_data_type_code) data_type_length,
      dvg.name dvg,
      case
      when tc.attribute_name='DVG_NUMBER'
      then substr(dvg.name,1,8)||'.'
      when dq.question_data_type_code = 'NUMBER'
      then dq.length+least(1,dq.decimal_places) || '.' ||
      dq.decimal_places
      when dq.question_data_type_code in ('CHAR','DATE')
      then '$'||dq.length||'.'
      else to_char(Null)
      end as sas_format
      from template_columns tc, view_template_questions vtq,
      view_question_mappings vqm, dcm_questions dq,
      discrete_value_groups dvg, dci_modules dm, dcis i
      where tc.template_id = &tid1
      and tc.template_question_id = vtq.view_template_question_id
      and vtq.view_template_question_id = vqm.parent_question_id
      and vtq.question_id = vqm.question_id
      and vqm.dcm_question_id = dq.dcm_question_id
      and vtq.occurrence_sn = dq.occurrence_sn
      and dq.discrete_val_grp_id = dvg.discrete_value_grp_id (+)
      and dq.discrete_val_grp_subset_nm=dvg.discrete_val_grp_subset_num (+)
      and dq.dcm_id = dm.dcm_id
      and dq.dcm_que_dcm_subset_sn = dm.dcm_subset_sn
      and dm.dci_id = i.dci_id



      Thank you.

      Shiva