This discussion is archived
0 Replies Latest reply: Feb 9, 2010 8:14 AM by 751767 RSS

Usage of Multiple Join Statements

751767 Newbie
Currently Being Moderated
Hi Chaitanya,

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

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, 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
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, oracle_name, tc.sas_name, tc.sas_label, tc.attribute_name,
decode(substr(,1,4),'PAGE',to_char(to_number(substr(,5))), pages,
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,
when tc.attribute_name='DVG_NUMBER'
then substr(,1,8)||'.'
when dq.question_data_type_code = 'NUMBER'
then dq.length+least(1,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.



  • Correct Answers - 10 points
  • Helpful Answers - 5 points