I have the update query like this
UPDATE fi_stg_instr_ref_dim a
SET
(first_par_call_dt,
pre_refunded_dt
) =
(SELECT min_call_dt.first_par_call_dt, exe_cd.pre_refunded_dt
FROM ( SELECT MIN (cs.call_dt) AS first_par_call_dt,
cs.fi_instrument_id AS fi_instrument_id
FROM call_schedule cs
WHERE cs.call_price = 100
GROUP BY cs.fi_instrument_id) min_call_dt,
(SELECT cs.call_dt AS pre_refunded_dt,
cs.fi_instrument_id AS fi_instrument_id
FROM call_schedule cs
WHERE cs.exercise_cd = 'P') exe_cd
WHERE a.fi_instrument_id = min_call_dt.fi_instrument_id(+)
AND a.fi_instrument_id = exe_cd.fi_instrument_id(+)
AND a.desk_cd = 'PREV'
AND a.pricing_dt = '10dec2013')
WHERE a.pricing_dt = '10dec2013' AND a.desk_cd = 'PREV'
and a.fi_instrument_id = 2005090900058804
So this update query is not updating the correct value for the first_par_call_dt for an unknown reason even though the value is present in call_schedule .
To debug the issue as why first_par_call_dt is updated as null ,I just ran the below select query and the select query returns value for
the first_par_call_dt.So totally confused as why the update is not working?
SELECT min_call_dt.first_par_call_dt, exe_cd.pre_refunded_dt
FROM fi_stg_instr_ref_dim a,( SELECT MIN (cs.call_dt) AS first_par_call_dt,
cs.fi_instrument_id AS fi_instrument_id
FROM call_schedule cs
WHERE cs.call_price = 100
GROUP BY cs.fi_instrument_id) min_call_dt,
(SELECT cs.call_dt AS pre_refunded_dt,
cs.fi_instrument_id AS fi_instrument_id
FROM call_schedule cs
WHERE cs.exercise_cd = 'P') exe_cd
WHERE a.fi_instrument_id = min_call_dt.fi_instrument_id(+)
AND a.fi_instrument_id = exe_cd.fi_instrument_id(+)
AND a.desk_cd = 'PREV'
AND a.pricing_dt = '10dec2013'
and a.fi_instrument_id = 2005090900058804
O/p of above select is
FIRST_PAR_CALL_DT | PRE_REFUNDED_DT |
8/1/2015 | |
Not able to understand what made the select query retrieve the value whcih the update query is not able to do .
please help.