Hi All,
Can anyone pl help me to understand how the diff tables in this tables are joined ( even 1 join would help) as I am not able to differentiate where the first join ended and whether the result is attched to the previous one or what all :(
I am little confused about how to convert this query in ANSI to the traditional (+) sysntax as I am not able to understannd which table is joined to which table and where exactly the previous join ends.
select distinct a.instrument_id "INSTRUMENT ID",
a.name "DESCRIPTION",
a.DEBT_PRIORITY_CLASS "DEBT PRIORITY CLASS",
c.alias "ISIN",
b.alias "MDSCURVE"
from (select distinct i.instrument_id,
i.name,
case
when (mn2.display_name != 'DEBT PRIORITY CLASS' and
mn2.display_name is not null) then
mn2.display_name
else
mn1.display_name
end "DEBT_PRIORITY_CLASS"
from instrument i, inst_debt id
left join marsnode mn1 on (id.debt_priority_class_id = mn1.node_id and
mn1.close_date is null and
mn1.type_id = 58412926883279)
left join marsnodelink mnl1 on (mn1.node_id = mnl1.node_id and
mnl1.close_date is null and
mnl1.begin_cob_date <=
TO_DATE('27-Oct-2010',
'DD-Mon-YYYY') and
mnl1.end_cob_date >
TO_DATE('27-Oct-2010',
'DD-Mon-YYYY'))
left join marsnode mn2 on (mnl1.parent_id = mn2.node_id and
mn2.close_date is null and
mn2.type_id = 58412926883279)
where i.instrument_id = id.instrument_id
and i.end_cob_date > TO_DATE('27-Oct-2010', 'DD-Mon-YYYY')
and i.close_action_id is null
and i.product_sub_type_id = 3
and i.begin_cob_date <= TO_DATE('27-Oct-2010', 'DD-Mon-YYYY')
and i.instrument_vn = id.instrument_vn) a
left outer join (select i.instrument_id, ia.alias as alias
from instrument i, inst_alias ia, domain d
where i.instrument_id = ia.instrument_id
and ia.domain_id = d.domain_id
and d.name = 'MDSCURVE'
and i.close_action_id is null
and i.product_sub_type_id = 3
and i.begin_cob_date <=
TO_DATE('27-Oct-2010', 'DD-Mon-YYYY')
and i.end_cob_date >
TO_DATE('27-Oct-2010', 'DD-Mon-YYYY')) b on (a.instrument_id =
b.instrument_id)
left outer join (select i.instrument_id, ia.alias as alias
from instrument i, inst_alias ia, domain d
where i.instrument_id = ia.instrument_id
and ia.domain_id = d.domain_id
and d.name = 'ISIN'
and i.close_action_id is null
and i.product_sub_type_id = 3
and i.begin_cob_date <=
TO_DATE('27-Oct-2010', 'DD-Mon-YYYY')
and i.end_cob_date >
TO_DATE('27-Oct-2010', 'DD-Mon-YYYY')) c on (a.instrument_id = c.instrument_id)
Specialy the problem with the first left outer join (from instrument i, inst_debt id) , there are many outer joins and i am not able to understand this thing.
Please help me to understand this thing.
Rgds,
Aashish