I like to use inner join and outer join among multiple tables on UDB...
For this, I wrote below, but it basically picking up too many duplicated records. basically I do not like to have duplicated entry from orders_history, just add 4 external IDs from instrument_ext_ref for each types if exist, if not, just put NULL there.
Would you please let me know how I should restrict?
I've tried to use distinct but then sql doesn't return value, maybe looping....
--------------------------
select * from orders_history o
inner join instrument i
on o.instrument_id = i.instrument_id
and o.date_order between '20070201' and '20070228'
and o.status = '3'
left outer join instrument_ext_ref ref_1
on o.instrument_id = ref_1.instrument_id
AND ref_1.instrument_id IS NOT NULL
and ref_1.ext_id_type = 'CUSIP'
left outer join instrument_ext_ref ref_2
on o.instrument_id = ref_2.instrument_id
AND ref_2.instrument_id IS NOT NULL
and ref_2.ext_id_type = 'SEDOL'
left outer join instrument_ext_ref ref_3
on o.instrument_id = ref_2.instrument_id
AND ref_3.instrument_id IS NOT NULL
and ref_3.ext_id_type = 'ISIN'
left outer join instrument_ext_ref ref_4
on o.instrument_id = ref_4.instrument_id
AND ref_4.instrument_id IS NOT NULL
and ref_4.ext_id_type = 'QUICK'
---------------
Start Free Trial