Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

select is working but update not

956118Dec 11 2013 — edited Dec 11 2013


  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_DTPRE_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.
      

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 8 2014
Added on Dec 11 2013
5 comments
394 views