6 Replies Latest reply: Dec 4, 2012 3:49 PM by Frank Kulash RSS

    ORA-01407 with UPDATE using COALESCE function

    ltzwoman
      Hi

      We're trying to run the following query as part of an upgrade:

      UPDATE LAWSON.NATBALANCE
      SET STATEMENT_REQ=(SELECT COALESCE(acm.STATEMENT_REQ,' ')
      FROM LAWSON.ARCUSTOMER acm
      WHERE acm.NAT_FLAG='N'
      AND LAWSON.NATBALANCE.NAT_COMPANY=acm.COMPANY AND
      LAWSON.NATBALANCE.NAT_CUSTOMER=acm.CUSTOMER)

      But we're getting the following error:

      ORA-01407: cannot update ("LAWSON"."NATBALANCE"."STATEMENT_REQ") to null.

      I’m confused by this for a couple of reasons. First, the STATEMENT_REQ field in LAWSON.ARCUSTOMER contains no NULLS, and second I thought the whole purpose of the COALESCE function was to return the first non-null result. What am I missing here?

      Edited by: ltzwoman on Dec 4, 2012 10:48 AM
        • 1. Re: ORA-01407 with UPDATE using COALESCE function
          Vivek L
          ltzwoman wrote:
          Hi

          We're trying to run the following query as part of an upgrade:

          UPDATE LAWSON.NATBALANCE
          SET STATEMENT_REQ=(SELECT COALESCE(acm.STATEMENT_REQ,' ')
          FROM LAWSON.ARCUSTOMER acm
          WHERE acm.NAT_FLAG='N'
          AND LAWSON.NATBALANCE.NAT_COMPANY=acm.COMPANY AND
          LAWSON.NATBALANCE.NAT_CUSTOMER=acm.CUSTOMER)

          But we're getting the following error:

          ORA-01407: cannot update ("LAWSON"."NATBALANCE"."STATEMENT_REQ") to null.

          I’m confused by this for a couple of reasons. First, the STATEMENT_REQ field in LAWSON.ARCUSTOMER contains no NULLS, and second I thought the whole purpose of the COALESCE function was to return the first non-null result. What am I missing here?

          Edited by: ltzwoman on Dec 4, 2012 10:48 AM
          Your update has no where clause and hence would update all records of LAWSON.NATBALANCE table.
          Possibly, there are rows in LAWSON.NATBALANCE table that do not match where condition of subquery
          WHERE acm.NAT_FLAG='N'
          AND LAWSON.NATBALANCE.NAT_COMPANY=acm.COMPANY AND
          LAWSON.NATBALANCE.NAT_CUSTOMER=acm.CUSTOMER)
          and are getting updated as null.
          • 2. Re: ORA-01407 with UPDATE using COALESCE function
            Solomon Yakobson
            Your UPDATE, the way it is written, updates all rows in table LAWSON.NATBALANCE. Most likely you want:
            UPDATE LAWSON.NATBALANCE 
               SET STATEMENT_REQ = (
                                    SELECT  COALESCE(acm.STATEMENT_REQ,' ')
                                      FROM  LAWSON.ARCUSTOMER acm
                                      WHERE acm.NAT_FLAG = 'N'
                                        AND LAWSON.NATBALANCE.NAT_COMPANY = acm.COMPANY
                                        AND LAWSON.NATBALANCE.NAT_CUSTOMER = acm.CUSTOMER
                                   )
              WHERE (
                     LAWSON.NATBALANCE.NAT_COMPANY,
                     LAWSON.NATBALANCE.NAT_CUSTOMER
                    ) IN (
                          SELECT  acm.COMPANY,
                                  acm.CUSTOMER
                            FROM  LAWSON.ARCUSTOMER acm
                            WHERE acm.NAT_FLAG = 'N'
                         )
            /
            SY.
            • 3. Re: ORA-01407 with UPDATE using COALESCE function
              ltzwoman
              Hi

              Thanks for the quick response, but the Lawson consultant is saying it is their intention to update all rows in the table. Our confusion lies with the ORA-01407 error as there are no NULL records for that field in LAWSON.ARCUSTOMER.
              • 4. Re: ORA-01407 with UPDATE using COALESCE function
                Frank Kulash
                Hi,

                Welcome to the forum!

                Instead of UPDATE, you might prefer to do this with MERGE:
                MERGE INTO  lawson.netbalance     dst
                USING   (
                         SELECT  company
                         ,         customer
                         ,         COALESCE ( statement_req
                                            , ' '
                                    ) AS statement_req_not_null
                         FROM    lawson.arcustomer
                     )               src
                ON     (   src.company          = dst.nat_company
                     AND src.customer     = dst.customer
                     )
                WHEN MATCHED THEN UPDATE
                SET     dst.statement_req     = src.statement_req_not_null
                WHERE     dst.statement_req      != src.statement_req_not_null
                ;
                You may find this easier to debug and maintain, since in an UPDATE statement, such as the one suggested by Solomon, the sub-query in the SET clause has to be repeated in the WHERE clause.


                 

                I hope this answers your question.
                If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
                If you're asking about a DML statement, such as UPDATE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
                Explain, using specific examples, how you get those results from that data.
                Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
                See the forum FAQ {message:id=9360002}
                • 5. Re: ORA-01407 with UPDATE using COALESCE function
                  kendenny
                  Just want to add the COALESCE with only two arguments is exactly the same as NVL, so you can save yourself some keystrokes by using NVL.
                  • 6. Re: ORA-01407 with UPDATE using COALESCE function
                    Frank Kulash
                    Hi,
                    kendenny wrote:
                    Just want to add the COALESCE with only two arguments is exactly the same as NVL, so you can save yourself some keystrokes by using NVL.
                    They're not exactly the same, and the time you save in coding can be lost when you run it.

                    COALESCE can be more efficient, beacuase NVL always evaluates both arguements, but COALESCE only evaluates arguments if all the preceding arguments were NULL. For the same reason, NVL might raise a run-time error in situations where COALESCE would not. In short, COALESCE is better, but, often, it's hard to notice.

                    So to be excruciatingly correct, you might say that when NVL works, it returns the same results as COALESCE.