Home » SQL & PL/SQL » SQL & PL/SQL » table update error (oracle9i)
table update error [message #337408] Wed, 30 July 2008 16:49 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Hello,

here is my update statement. The field FK_ORIG_BNKID is not null column in INVENT_ITEM table. The subquery is populating null values and it is throwing error.

SQL> 
SQL> UPDATE INVENT_ITEM sfw
  2       SET (fk_orig_bnkid )
  3         = (SELECT b.pk_bnkid 
  4            FROM   issuer i,
  5             countries c,
  6                   bank b,
  7                   manager m,
  8                   security s3,
  9                   series s4
 10            WHERE  s4.pk_secid = sfw.series_id
 11            AND    s3.pk_secid = s4.reference
 12            AND    m.fk_secid = s3.pk_secid
 13            AND    m.fk_bnkid = sfw.fk_orig_bnkid
 14            AND    m.fk_flgid = 5 -- BookMgr
 15            AND    b.pk_bnkid = m.fk_bnkid
 16            AND    i.pk_issid(+) = s3.fk_issid
 17            AND    c.country(+) = i.country)
 18       WHERE (sfw.series_id, sfw.manager_id) IN (SELECT s.series_id, s.manager_id
 19                                                 FROM   INVENT_ITEM s, 
 20                (select pk_secid, fk_flgid, dealtype, currency, status, rank, amountoffered,
 21                issueprice, coupontype, assetclass, rating, igm_rpt_load
 22                    FROM   security
 23                     WHERE  igm_rpt_load = 'N'
 24                      AND    fk_flgid IN (1,2,3)) t
 25                                                 WHERE  t.fk_flgid = 3
 26                                                 AND    t.status = 'Priced'
 27                                                 AND    t.assetclass IS NOT NULL
 28                                                 AND    s.pk_secid = t.pk_secid)
 29  /
     SET (fk_orig_bnkid )
          *
ERROR at line 2:
ORA-01407: cannot update ("IGM_RPT"."INVENT_ITEM"."FK_ORIG_BNKID")
to NULL



Now i need to find out which where CLAUSE is causing an issue. So i am running the below sub query.


SQL> SELECT b.pk_bnkid 
  2        FROM   issuer i,
  3         countries c,
  4               bank b,
  5               manager m,
  6               security s3,
  7               series s4,
  8       INVENT_ITEM sfw
  9        WHERE  s4.pk_secid = sfw.series_id
 10        AND    s3.pk_secid = s4.reference
 11        AND    m.fk_secid = s3.pk_secid
 12        AND    m.fk_bnkid = sfw.fk_orig_bnkid
 13        AND    m.fk_flgid = 5  
 14        AND    b.pk_bnkid = m.fk_bnkid
 15        AND    i.pk_issid(+) = s3.fk_issid
 16        AND    c.country(+) = i.country
 17        AND (sfw.series_id, sfw.manager_id) IN (SELECT s.series_id, s.manager_id
 18                                             FROM   INVENT_ITEM s, 
 19            (select pk_secid, fk_flgid, dealtype, currency, status, rank, amountoffered,
 20            issueprice, coupontype, assetclass, rating, igm_rpt_load
 21                FROM   security
 22                 WHERE  igm_rpt_load = 'N'
 23                  AND    fk_flgid IN (1,2,3)) t
 24                                             WHERE  t.fk_flgid = 3
 25                                             AND    t.status = 'Priced'
 26                                             AND    t.assetclass IS NOT NULL
 27                                             AND    s.pk_secid = t.pk_secid)
 28  /

no rows selected

SQL> 


The above query is returning no rows... what am i missing in the query? Any help appreciated.
Re: table update error [message #337409 is a reply to message #337408] Wed, 30 July 2008 16:56 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Eliminate/remove
issuer i,
countries c,
manager m,
security s3,
series s4,
INVENT_ITEM sfw
out of the FROM clause.
They return no data to the SELECT & should not be present in the FROM clause.
If data from these table is required for correct filtering,
these tables need to be subordinated into the WHERE clause.

Without table DDL & sample data DML, nobody can say why no rows get returned as clearly stated in Posting Guidelines you ignored.

[Updated on: Wed, 30 July 2008 16:58] by Moderator

Report message to a moderator

Re: table update error [message #337440 is a reply to message #337408] Wed, 30 July 2008 22:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If I may, let me clarify Anacendent's point about this being a pretty bad query, by asking this question:

what does this join condition do? at line 16

AND    c.country(+) = i.country

You think about it and then get back to us.

Also, for your problem, my money starts here:

 27                                                 AND    t.assetclass IS NOT NULL


Good luck, Kevin
Re: table update error [message #337691 is a reply to message #337440] Thu, 31 July 2008 12:51 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
There was a disconnect in one of the table in the update statement sub query and it is fixed. Thanks
Previous Topic: UTL_FILE overwrites data
Next Topic: Number in date column
Goto Forum:
  


Current Time: Sat Dec 03 12:15:01 CST 2016

Total time taken to generate the page: 0.09098 seconds