Home » SQL & PL/SQL » SQL & PL/SQL » How to see/check the null records for this update statement; getting ORA-01407 error ? (oracle 9.2.0.1)
How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311591] Fri, 04 April 2008 21:47 Go to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
How to see/check the null records for this update statement; getting ORA-01407 error ?

I have tried but not able to get / am not sure doing right way...could any one please help..

ORA-01407: cannot update ("MIMI"."MON_PLAN"."PLAN_YTD_01") to NULL

UPDATE /*+ INDEX(M MON_PLAN_IDX_PK)
        NOPARALLEL(M) NOPARALLEL_INDEX(M MON_PLAN_IDX_PK) */
MON_PLAN M
SET (plan_ytd_01,
plan_ytd_02,
plan_ytd_03,
plan_ytd_04,
plan_ytd_05,
plan_ytd_06,
plan_ytd_07,
plan_ytd_08,
plan_ytd_09,
plan_ytd_10,
plan_ytd_11,
plan_ytd_12) =
(SELECT /*+ ORDERED INDEX(T MON_PLAN_IDX_PK) USE_NL (T)
                    INDEX(C COA_REF_M_IDX_PK) USE_NL(C)
                    NOPARALLEL(T) NOPARALLEL_INDEX(T MON_PLAN_IDX_PK) */
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 ) ) ,2),
       (M.plan_mtd_01 )), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1)))
                    / (1 +59) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31))
                    / (1 +90) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30))
                    / (1 +120) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31))
                    / (1 +151) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05)), 0)
         ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30))
                    / (1 +181) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31))
                    / (1 +212) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31))
                    / (1 +243) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30))
                    / (1 +273) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30)
                    + (M.plan_mtd_10 * 31))
                    / (1 +304) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09
                    +  M.plan_mtd_10)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30)
                    + (M.plan_mtd_10 * 31)
                    + (M.plan_mtd_11 * 30))
                    / (1 +334) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09
                    +  M.plan_mtd_10
                    +  M.plan_mtd_11)), 0)
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30)
                    + (M.plan_mtd_10 * 31)
                    + (M.plan_mtd_11 * 30)
                    + (M.plan_mtd_12 * 31))
                    / (1 +365) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09
                    +  M.plan_mtd_10
                    +  M.plan_mtd_11
                    +  M.plan_mtd_12)), 0)
FROM MON_PLAN T, COA_REF_M C
WHERE
        T.REC_TYPE      = M.REC_TYPE
AND     T.ENT           = M.ENT
AND     T.SUB_ENT       = M.SUB_ENT
AND     T.POST_ACCT     = M.POST_ACCT
AND     T.CTR_OR_HIER   = M.CTR_OR_HIER
AND     T.YR            = M.YR
AND     T.PLAN_TYPE     = M.PLAN_TYPE
AND     T.SUM_FLAG      = M.SUM_FLAG
AND     T.ADJ           = M.ADJ
AND     T.PRODUCT_CODE  = M.PRODUCT_CODE
AND     T.SEGMENT_CODE  = M.SEGMENT_CODE
AND     T.POST_ACCT     = C.ACCT
AND     C.ACCT_LVL      = '8'
AND     C.ACCT_ID       = 'primary'
AND     C.REL_TYPE      = ' ')
WHERE
        M.REC_TYPE      = 'A'
AND     M.ENT           = '9'
AND     M.SUB_ENT       = '0'
AND     M.POST_ACCT     like '8%'
AND     M.CTR_OR_HIER   like '%'
AND     M.YR            = '2008'
AND     M.PLAN_TYPE     = 'RAMBO'
AND     M.SUM_FLAG      LIKE '%'
AND     M.ADJ           >= ' '
AND     M.PRODUCT_CODE  = 'ALL'
AND     M.SEGMENT_CODE  = 'ALL';

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311596 is a reply to message #311591] Fri, 04 April 2008 23:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Have a look at
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#sthref9455
especially the section entitled:
"Inserting Into a Table with Error Logging: Example"
You could use this approach to identify the problem row(s).

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311615 is a reply to message #311591] Sat, 05 April 2008 02:00 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Maybe a better alternative would be this: as you issue NVL on all columns in the subquery, the NULL rows are caused by non-existence of corresponding row in T and C tables. It is also specified in http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2073065.

The solution: copy your subquery into the WHERE condition (you may replace the SELECT columns with anything, eg. 1): 
UPDATE MON_PLAN M
SET (<column list>) = (<subquery>)
WHERE <current conditions on M>
  AND EXISTS( SELECT 1
                FROM MON_PLAN T, COA_REF_M C
                WHERE <conditions from the subquery> )

If you want to update rows without join to T and C tables with NULLs (zeros or whatever), then issue another UPDATE statement like this: 
UPDATE MON_PLAN M
SET (<column list>) = (<list of NULLs, zeros, etc.>)
WHERE <current conditions on M>
  AND NOT EXISTS( SELECT 1
                  FROM MON_PLAN T, COA_REF_M C
                  WHERE <conditions from the subquery> )

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311633 is a reply to message #311591] Sat, 05 April 2008 04:12 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Just in addition, maybe the best approach would be using subquery in DML table expression clause: 
UPDATE (
  SELECT <updated columns in M>, <subquery columns>
  FROM MON_PLAN M, MON_PLAN T, COA_REF_M C
  WHERE <conditions from the subquery>
)
SET <column 1 from M> = <subquery column 1>,
    <column 2 from M> = <subquery column 2>, ...
WHERE <current conditions on M>;

Using OUTER JOIN, you could update all rows in M (no need of the second UPDATE).

[Edit: typo]

[Updated on: Sat, 05 April 2008 04:12]

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311655 is a reply to message #311633] Sat, 05 April 2008 08:48 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Here I want to indentify those / that record & want to remove from the set. but now I want how to identify the null record using the same select; instead of update want to select the same set of records.?.

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311685 is a reply to message #311596] Sat, 05 April 2008 13:12 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Here its showing for insert statement / but not able to find for UPDATE clause, please help me

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311689 is a reply to message #311591] Sat, 05 April 2008 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Here its showing for insert statement / but not able to find for UPDATE clause, please help me

I doubt you looked hard, if at all

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#sthref9987


error_logging_clause

The error_logging_clause has the same behavior in an UPDATE statement as it does in an INSERT statement. Please refer to the INSERT statement error_logging_clause for more information.

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311691 is a reply to message #311689] Sat, 05 April 2008 14:34 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
I have tried using that update error tracing clause its getting error

ERROR at line 238:
ORA-00904: "PM01": invalid identifier


15:31:02 SQL> 

var pmm01 number;

set serveroutput on;

UPDATE /*+ INDEX(M MON_PLAN_IDX_PK)
        NOPARALLEL(M) NOPARALLEL_INDEX(M MON_PLAN_IDX_PK) */
MON_PLAN M
SET (plan_ytd_01,
plan_ytd_02,
plan_ytd_03,
plan_ytd_04,
plan_ytd_05,
plan_ytd_06,
plan_ytd_07,
plan_ytd_08,
plan_ytd_09,
plan_ytd_10,
plan_ytd_11,
plan_ytd_12) =
(SELECT /*+ ORDERED INDEX(T MON_PLAN_IDX_PK) USE_NL (T)
                    INDEX(C COA_REF_M_IDX_PK) USE_NL(C)
                    NOPARALLEL(T) NOPARALLEL_INDEX(T MON_PLAN_IDX_PK) */
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 ) ) ,2),
       (M.plan_mtd_01 )), 0) pm01
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1)))
                    / (1 +59) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02)), 0) pm02
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31))
                    / (1 +90) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03)), 0) pm03
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30))
                    / (1 +120) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04)), 0) pm04
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31))
                    / (1 +151) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05)), 0) pm05
         ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30))
                    / (1 +181) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06)), 0) pm06
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31))
                    / (1 +212) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07)), 0) pm07
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31))
                    / (1 +243) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08)), 0) pm08
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30))
                    / (1 +273) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09)), 0) pm09
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30)
                    + (M.plan_mtd_10 * 31))
                    / (1 +304) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09
                    +  M.plan_mtd_10)), 0) pm10
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30)
                    + (M.plan_mtd_10 * 31)
                    + (M.plan_mtd_11 * 30))
                    / (1 +334) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                    +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09
                    +  M.plan_mtd_10
                    +  M.plan_mtd_11)), 0) pm11
        ,
       NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
        'B', ROUND(((M.plan_mtd_01 * 31)
                    + (M.plan_mtd_02 * (28 + 1))
                    + (M.plan_mtd_03 * 31)
                    + (M.plan_mtd_04 * 30)
                    + (M.plan_mtd_05 * 31)
                    + (M.plan_mtd_06 * 30)
                    + (M.plan_mtd_07 * 31)
                    + (M.plan_mtd_08 * 31)
                    + (M.plan_mtd_09 * 30)
                    + (M.plan_mtd_10 * 31)
                    + (M.plan_mtd_11 * 30)
                    + (M.plan_mtd_12 * 31))
                    / (1 +365) ,2),
                      (M.plan_mtd_01
                    +  M.plan_mtd_02
                     +  M.plan_mtd_03
                    +  M.plan_mtd_04
                    +  M.plan_mtd_05
                    +  M.plan_mtd_06
                    +  M.plan_mtd_07
                    +  M.plan_mtd_08
                    +  M.plan_mtd_09
                    +  M.plan_mtd_10
                    +  M.plan_mtd_11
                    +  M.plan_mtd_12)), 0) pm12
FROM MON_PLAN T, COA_REF_M C
WHERE
        T.REC_TYPE      = M.REC_TYPE
AND     T.ENT           = M.ENT
AND     T.SUB_ENT       = M.SUB_ENT
AND     T.POST_ACCT     = M.POST_ACCT
AND     T.CTR_OR_HIER   = M.CTR_OR_HIER
AND     T.YR            = M.YR
AND     T.PLAN_TYPE     = M.PLAN_TYPE
AND     T.SUM_FLAG      = M.SUM_FLAG
AND     T.ADJ           = M.ADJ
AND     T.PRODUCT_CODE  = M.PRODUCT_CODE
AND     T.SEGMENT_CODE  = M.SEGMENT_CODE
AND     T.POST_ACCT     = C.ACCT
AND     C.ACCT_LVL      = '8'
AND     C.ACCT_ID       = 'primary'
AND     C.REL_TYPE      = ' ')
WHERE
        M.REC_TYPE      = 'A'
AND     M.ENT           like '910'
AND     M.SUB_ENT       = '000'
AND     M.POST_ACCT     like '8%'
AND     M.CTR_OR_HIER   like    '%'
AND     M.YR            = '2008'
AND     M.PLAN_TYPE     like 'ztest'
AND     M.SUM_FLAG      LIKE '%'
AND     M.ADJ           >= ' '
AND     M.PRODUCT_CODE  = 'ALL'
AND     M.SEGMENT_CODE  = 'ALL'
RETURNING pm01 into pmm01

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311695 is a reply to message #311591] Sat, 05 April 2008 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have tried using that update error tracing clause its getting error
Please realize that especially when it comes to computers "words matter".

Please clarify what in the world you mean when you state "I have tried using that update error tracing clause"

What "error tracing clause"?

Yes you changed your code, but the change had NOTHING to do with anything involving "error" whatever.

What were you expecting, planning or hoping would happen by adding
"RETURNING pm01 into pmm01"
to the UPDATE statement?

Why can't/won't/don't you use CUT & PASTE to show whole SQL*Plus session, so we can see EXACTLY what you are doing & how Oracle responds?

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311713 is a reply to message #311689] Sat, 05 April 2008 21:16 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
the RETURING clause- the example given for single update statement, will it work for correlated dml statement like here ?.

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311715 is a reply to message #311591] Sat, 05 April 2008 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since you won't answer my questions, I won't waste my time responding to your questions any more.

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311717 is a reply to message #311715] Sat, 05 April 2008 22:48 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Sorry for delayed response; its actually in production database & pwd was locked due to some reason in the middle of work. so, unable to do that one right now, because it will take one day for re-set pwd for active we have certain process for this. sorry for your in-convinence and thank you for providing excellent support.

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311768 is a reply to message #311695] Sun, 06 April 2008 13:01 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Here is the output fetched from sql session for the statement
still its giving the same error instead of locating specific point...

SQL> @UPDATE_TEST_SQL.sql;
UPDATE /*+ INDEX(M MON_PLAN_IDX_PK)
*
ERROR at line 1:
ORA-01407: cannot update ("INSIGHT"."MON_PLAN"."PLAN_YTD_01") to NULL
 
 
 
M_REC_TYPE
--------------------------------
 
 
 
M_ENT
--------------------------------
 
 
 
M_SUB_ENT
--------------------------------
 
 
 
M_POST_ACCT
--------------------------------
 
 
 
M_CTR_OR_HIER
--------------------------------
 
 
 
M_YR
--------------------------------
 
 
 
M_PLAN_TYPE
--------------------------------
 
 
 
M_SUM_FLAG
--------------------------------
 
 
 
M_ADJ
--------------------------------
 
 
 
M_PRODUCT_CODE
--------------------------------
 
 
 
M_SEGMENT_CODE
--------------------------------
 

SQL>

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311808 is a reply to message #311591] Sun, 06 April 2008 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The correct use of Error Logging clause will identify the record(s) causing the error.

Of course if you wish to continue to flail blindly, continue to ignore this suggestion & solve your mystery any other way deemed "better".

Report message to a moderator

Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311835 is a reply to message #311591] Mon, 07 April 2008 01:40 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
from email
Could you please help me, here I want issue the update same functionality without nulls;

You given three approch, but I do not know which one is sutable for my case, could you please advice me the exact one plz..

Just a slight correction: I gave only two approaches: correlated subquery (you use, both queries in my first post) or correlated update (query my second post).

There are two possible scenarios of your update:
S1) update all rows in M with <current conditions on M>
S2) restrict S1 scope only to the rows which have representation in T and C tables
I do not know, which is your case; but they do not differ a lot, so I described both.

From my point of view, correlated update is better; you may even display the results before UPDATE. In case you want to follow scenario S1, you just need to use OUTER JOIN instead of INNER JOIN. See details in http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054014.

[Edit: typo]

[Updated on: Mon, 07 April 2008 01:40]

Report message to a moderator

Previous Topic: BLOB value null: empty locator
Next Topic: ddl for a role Plus role privs/grants
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Aug 23 07:06:20 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.