Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2013
    Posts
    1

    Unanswered: Need help in db2

    I am new to Db2. I am trying to make below query compatible to DB2, but not able to do it successfully.

    UPDATE table_num_scheme SET next_value = DECODE( sign (DECODE( RESET_PERIOD, 0,-1,null,-1,(sysdate - (RESET_TIME + NUMTODSINTERVAL(RESET_PERIOD,'MINUTE'))))), 1, start_value + 1, -1, next_value + 1),RESET_TIME = DECODE( sign (DECODE( RESET_PERIOD, 0,-1,null,-1,(sysdate - (RESET_TIME + NUMTODSINTERVAL(RESET_PERIOD,'MINUTE'))))), 1, sysdate, -1, RESET_TIME) WHERE name = 'AAA'

    Please help me .

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've reformatted your original code to:
    Code:
    UPDATE table_num_scheme
       SET
          next_value =
             DECODE(
                sign (
                   DECODE(RESET_PERIOD
    ,                 0, -1
    ,                 null, -1
    ,                 (sysdate - (RESET_TIME + NUMTODSINTERVAL(RESET_PERIOD,'MINUTE')))
                   )
                )
    ,           +1, start_value + 1
    ,           -1, next_value + 1
             )
    ,     RESET_TIME = 
             DECODE( 
                sign(
                   DECODE(RESET_PERIOD
    ,                 0, -1
    ,                 null, -1
    ,                 (sysdate - (RESET_TIME + NUMTODSINTERVAL(RESET_PERIOD,'MINUTE')))
                   )
                )
    ,              +1, sysdate
    ,              -1, RESET_TIME
             )
       WHERE name = 'AAA'
    Taking advantage of DB2 NULL handling, I'd use:
    Code:
    UPDATE table_num_scheme
       SET
          next_value = CASE
             WHEN 0 <> RESET_PERIOD THEN next_value + 1
             ELSE start_value + 1
          END
    ,     RESET_TIME = 
             WHEN 0 <> RESET_PERIOD THEN CURRENT TIMESTAMP
             ELSE RESET_TIME
          END
       WHERE name = 'AAA'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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