All,
I have a query that hits the same tables 4 times, but with slightly different joins each time to reflect the different data required:
SELECT 'LEGS_01' as LEG, ISIN FROM ALL_EXPIRIES ae
JOIN OBLIGATION_CYCLE oc
ON ( ae.class_symbol = oc.market_code
AND ae.current_cycle_id = substr(oc.cycle_id,1,1)
AND ae.expiration_sequence_no = substr(oc.obligation_sequence,1,1)
)
UNION ALL
SELECT 'LEGS_02', ISIN FROM ALL_EXPIRIES ae
JOIN OBLIGATION_CYCLE oc
ON (ae.class_symbol = oc.market_code
AND ae.current_cycle_id = substr(oc.cycle_id,2,1)
AND ae.expiration_sequence_no = substr(oc.obligation_sequence,2,1)
)
UNION ALL
SELECT 'LEGS_03', ISIN FROM ALL_EXPIRIES ae
JOIN OBLIGATION_CYCLE oc
ON (ae.class_symbol = oc.market_code
AND ae.current_cycle_id = substr(oc.cycle_id,3,1)
AND ae.expiration_sequence_no = substr(oc.obligation_sequence,3,1)
)
UNION ALL
SELECT 'LEGS_04', ISIN FROM ALL_EXPIRIES ae
JOIN OBLIGATION_CYCLE oc
ON (ae.class_symbol = oc.market_code
AND ae.current_cycle_id = substr(oc.cycle_id,4,1)
AND ae.expiration_sequence_no = substr(oc.obligation_sequence,4,1)
)
Note the change in the joins' SUBSTR each time incrementing by 1 position.
Is there a better way of achieving this?
Jason.