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.


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

Legend

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