Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Update Statement running forever

    I am getting following error message when I execute the below Update Query...

    Lookup Error - DB2 Database Error: ERROR [42704] [IBM][DB2/NT64] SQL0204N "F_SALESFACT_NEWVERSION.SLQNTY" is an undefined name.

    UPDATE F_SALESFACT_NEWVERSION
    SET SLQNTY = (
    SELECT newqty.SCQNTY, s
    FROM SHTCHGHST newqty
    JOIN (
    SELECT n.SCQNTY,t.SLQNTY, n.SCDVSN, n.SCACCT, n."SCINV#", n.SCITEM, n.SCTDAT, n.SCASHT
    FROM F_SALESFACT_NEWVERSION t
    JOIN SHTCHGHST n ON t.DISTCENTER = n.SCDVSN AND t.SLACCT = n.SCACCT AND t."SLINV#" = n."SCINV#" AND t.SLITEM = n.SCITEM AND
    t.SLTDAT = n.SCTDAT AND t.SLSHRT = n.SCASHT
    WHERE F_SALESFACT_NEWVERSION.DISTCENTER = n.SCDVSN AND F_SALESFACT_NEWVERSION.SLACCT = n.SCACCT AND F_SALESFACT_NEWVERSION."SLINV#" = n."SCINV#" AND
    F_SALESFACT_NEWVERSION.SLITEM = n.SCITEM AND F_SALESFACT_NEWVERSION.SLTDAT = n.SCTDAT AND F_SALESFACT_NEWVERSION.SLSHRT = n.SCASHT AND
    F_SALESFACT_NEWVERSION.SLQNTY = n.SCQNTY AND
    n.SCCODE = 'O'
    ) stg ON newqty.SCDVSN = stg.SCDVSN AND newqty.SCACCT = stg.SCACCT AND newqty."SCINV#" = stg."SCINV#" AND newqty.SCITEM = stg.SCITEM AND newqty.SCTDAT = stg.SCTDAT AND
    newqty.SCASHT = stg.SCASHT
    WHERE newqty.SCCODE = 'N' AND newqty.SCTDAT = 20120203 AND newqty.SCASHT <> 'NONE'
    );
    Last edited by kellog1; 03-28-12 at 17:34.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It takes me forever to drive to work. My car is blue. Any suggestions where I am going wrong with it...

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by n_i View Post
    It takes me forever to drive to work.
    Work from home.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by n_i View Post
    It takes me forever to drive to work. My car is blue. Any suggestions where I am going wrong with it...
    Get a red car. Everyone knows those are faster.

  5. #5
    Join Date
    Sep 2009
    Posts
    2
    Any suggestions guys?

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    check whether the column named "SLQNTY" exists

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Kellog,
    I think you missed N_I's point. You say UPDATE statement running forever, then you show you are getting a -204, meaning column does not exist. Your SQL is unformatted and runs together. You use correlation on tables, then don't use that correlation, you go back to using the table name. How about try letting us know exactly what the problem is. Try using the Code tags, so that your SQL can be shown to us formatted.

    Dave

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    Work from home.
    I actually do. Although I'd have hard time calling it "work".

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by kellog1 View Post
    Any suggestions guys?
    That's not fair. You've changed the original question, now my humorous remark seems irrelevant and I look stupid.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The reason of SQL0204N must be that you added alias "t" for the table F_SALESFACT_NEWVERSION, but you used table name for qualifier.

    Anyhow, here is my trial.
    Example 1 ---> Example 2 ---> Example 3 are step by step revisions.
    Code:
    /***********************************************************************
    (1) Reformat and use alias "t" for qualifiers.
    ***********************************************************************/
    
    /***********************************************************************
    (2) Add alias("t") for a table to be updated(target table)
        and add a LATERAL keyword to see the alias("t") inside the subquery.
    ***********************************************************************/
    
    /**********************************************************************
    (3) Remove inner subselect and change some order of tables and columns.
    **********************************************************************/
    Example 1:
    Code:
    /***********************************************************************
    (1) Reformat and use alias "t" for qualifiers.
    ***********************************************************************/
    /*++++++++++++++++++++++++++++++++++++++++++++
    Last edited by kellog1; Today at 05:34. 
    +++++++++++++++++++++++++++++++++++++++++++++*/
    UPDATE F_SALESFACT_NEWVERSION
       SET SLQNTY =
          (SELECT newqty.SCQNTY /*, s*/
            FROM  SHTCHGHST newqty
            JOIN (SELECT n.SCQNTY
                       , t.SLQNTY
                       , n.SCDVSN
                       , n.SCACCT
                       , n.SCINV#
                       , n.SCITEM
                       , n.SCTDAT
                       , n.SCASHT
                   FROM  F_SALESFACT_NEWVERSION t
                   JOIN  SHTCHGHST n
                    ON   t.DISTCENTER = n.SCDVSN
                     AND t.SLACCT     = n.SCACCT
                     AND t.SLINV#     = n.SCINV#
                     AND t.SLITEM     = n.SCITEM
                     AND t.SLTDAT     = n.SCTDAT
                     AND t.SLSHRT     = n.SCASHT
                   WHERE t.DISTCENTER = n.SCDVSN
                     AND t.SLACCT     = n.SCACCT
                     AND t.SLINV#     = n.SCINV#
                     AND t.SLITEM     = n.SCITEM
                     AND t.SLTDAT     = n.SCTDAT
                     AND t.SLSHRT     = n.SCASHT
                     AND t.SLQNTY     = n.SCQNTY
                     AND n.SCCODE     = 'O'
                 ) stg
             ON   newqty.SCDVSN = stg.SCDVSN
              AND newqty.SCACCT = stg.SCACCT
              AND newqty.SCINV# = stg.SCINV#
              AND newqty.SCITEM = stg.SCITEM
              AND newqty.SCTDAT = stg.SCTDAT
              AND newqty.SCASHT = stg.SCASHT
            WHERE newqty.SCCODE = 'N'
              AND newqty.SCTDAT = 20120203
              AND newqty.SCASHT <> 'NONE'
          )
    ;

    Example 2:
    Code:
    /***********************************************************************
    (2) Add alias("t") for a table to be updated(target table)
        and add a LATERAL keyword to see the alias("t") inside the subquery.
    ***********************************************************************/
    /*++++++++++++++++++++++++++++++++++++++++++++
    Last edited by kellog1; Today at 05:34. 
    +++++++++++++++++++++++++++++++++++++++++++++*/
    UPDATE F_SALESFACT_NEWVERSION t
       SET SLQNTY =
          (SELECT newqty.SCQNTY /*, s*/
            FROM  SHTCHGHST newqty
            JOIN
                 LATERAL
                 (SELECT n.SCQNTY
                       , t.SLQNTY
                       , n.SCDVSN
                       , n.SCACCT
                       , n.SCINV#
                       , n.SCITEM
                       , n.SCTDAT
                       , n.SCASHT
                   FROM  SHTCHGHST n
                   WHERE t.DISTCENTER = n.SCDVSN
                     AND t.SLACCT     = n.SCACCT
                     AND t.SLINV#     = n.SCINV#
                     AND t.SLITEM     = n.SCITEM
                     AND t.SLTDAT     = n.SCTDAT
                     AND t.SLSHRT     = n.SCASHT
                   /*WHERE*/
                     AND t.SLQNTY     = n.SCQNTY
                     AND n.SCCODE     = 'O'
                 ) stg
             ON   newqty.SCDVSN = stg.SCDVSN
              AND newqty.SCACCT = stg.SCACCT
              AND newqty.SCINV# = stg.SCINV#
              AND newqty.SCITEM = stg.SCITEM
              AND newqty.SCTDAT = stg.SCTDAT
              AND newqty.SCASHT = stg.SCASHT
            WHERE newqty.SCCODE = 'N'
              AND newqty.SCTDAT = 20120203
              AND newqty.SCASHT <> 'NONE'
          )
    ;

    Example 3:
    Code:
    /**********************************************************************
    (3) Remove inner subselect and change some order of tables and columns.
    ***********************************************************************/
    /*++++++++++++++++++++++++++++++++++++++++++++
    Last edited by kellog1; Today at 05:34. 
    +++++++++++++++++++++++++++++++++++++++++++++*/
    UPDATE F_SALESFACT_NEWVERSION t
       SET SLQNTY =
          (SELECT newqty.SCQNTY
            FROM  SHTCHGHST stg
            INNER JOIN
                  SHTCHGHST newqty
             ON   newqty.SCDVSN = stg.SCDVSN
              AND newqty.SCACCT = stg.SCACCT
              AND newqty.SCINV# = stg.SCINV#
              AND newqty.SCITEM = stg.SCITEM
              AND newqty.SCTDAT = stg.SCTDAT
              AND newqty.SCASHT = stg.SCASHT
            /*WHERE*/
              AND newqty.SCCODE = 'N'
              AND newqty.SCTDAT = 20120203
              AND newqty.SCASHT <> 'NONE'
            WHERE stg.SCDVSN = t.DISTCENTER
              AND stg.SCACCT = t.SLACCT
              AND stg.SCINV# = t.SLINV#
              AND stg.SCITEM = t.SLITEM
              AND stg.SCTDAT = t.SLTDAT
              AND stg.SCASHT = t.SLSHRT
              AND stg.SCQNTY = t.SLQNTY
              AND stg.SCCODE = 'O'
          )
    /********************************************************************************
    It is not clear whether the following EXISTS predicate was neccesary or not.
    *********************************************************************************/
     WHERE EXISTS
          (SELECT 0 /*newqty.SCQNTY*/
            FROM  SHTCHGHST stg
            INNER JOIN
                  SHTCHGHST newqty
             ON   newqty.SCDVSN = stg.SCDVSN
              AND newqty.SCACCT = stg.SCACCT
              AND newqty.SCINV# = stg.SCINV#
              AND newqty.SCITEM = stg.SCITEM
              AND newqty.SCTDAT = stg.SCTDAT
              AND newqty.SCASHT = stg.SCASHT
            /*WHERE*/
              AND newqty.SCCODE = 'N'
              AND newqty.SCTDAT = 20120203
              AND newqty.SCASHT <> 'NONE'
            WHERE stg.SCDVSN = t.DISTCENTER
              AND stg.SCACCT = t.SLACCT
              AND stg.SCINV# = t.SLINV#
              AND stg.SCITEM = t.SLITEM
              AND stg.SCTDAT = t.SLTDAT
              AND stg.SCASHT = t.SLSHRT
              AND stg.SCQNTY = t.SLQNTY
              AND stg.SCCODE = 'O'
          )
    ;
    Last edited by tonkuma; 03-31-12 at 10:08. Reason: Replace qualifier "n." in Example 3 with "stg."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •