Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    7

    Unanswered: Addition inside of a function

    Hello,

    I'm trying right now to add two integers inside of an RIGHT function. The addition does work, but the RIGHT function is being ignored. Does anybody know how to solve this problem?

    Code:
            SET V_SQLMERGE = 
      'MERGE INTO ' ||
       @FACHTABELLE || ' AS A 
        USING (
         SELECT 
          ID, row_number() over(ORDER BY ID) AS ROWCOUNT
           FROM ' ||
            @FACHTABELLE ||
             ' WHERE KEIN_VERSAND IS NULL AND SENDUNGSID IS NULL) AS B 
              ON 
               (A.ID = B.ID) 
                WHEN MATCHED 
                 THEN 
                  UPDATE SET A.SENDUNGSID = ''' || V_SENDUNGSID || '''|| RIGHT(''00000000'' || ROWCOUNT + ''' || V_COUNTER || ''', 8)';
      EXECUTE IMMEDIATE V_SQLMERGE;
    I don't get an error or anything. The RIGHT function just doesn't work at all. Would be nice if someone could tell me what's wrong here.

  2. #2
    Join Date
    Jul 2012
    Posts
    7
    The problem were the zeroes .. which apparently doesn't allow mathematic operations. I solved this problem by adding the numbers up when I select the rows/ set the row_number function up.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The reason might be related to "Precedence of operations".
    SQL on DB2 is strong typed and heve not been supported automatic conversion between string and number until recently
    (I can't remember from what version/release supported it).

    DB2 10 - DB2 SQL - Precedence of operations
    Precedence of operations

    Expressions within parentheses are evaluated first.
    When the order of evaluation is not specified by parentheses,
    prefix operators are applied before multiplication and division,
    and multiplication, division, and concatenation are applied before addition and subtraction.
    Operators at the same precedence level are applied from left to right.
    So, in this code
    ...
    SET A.SENDUNGSID
    = ''' || V_SENDUNGSID || '''|| RIGHT(''00000000'' || ROWCOUNT + ''' || V_COUNTER || ''', 8)';
    concatenaion( '00000000' || ROWCOUNT ) might be precedent to addition( + ' <V_COUNTER> ' ).

    If this guess was right, try to add parentheses like ...
    Code:
    ...
    SET A.SENDUNGSID
      = ''' || V_SENDUNGSID
        || '''|| RIGHT(''00000000'' || (ROWCOUNT + ' || V_COUNTER || ') , 8)';
    Note: I thought that quotaion marks around V_COUNTER might be unnecessary.

Posting Permissions

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