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?
SET V_SQLMERGE =
'MERGE INTO ' ||
@FACHTABELLE || ' AS A
ID, row_number() over(ORDER BY ID) AS ROWCOUNT
FROM ' ||
' WHERE KEIN_VERSAND IS NULL AND SENDUNGSID IS NULL) AS B
(A.ID = B.ID)
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.
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).
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 beforeaddition and subtraction.
Operators at the same precedence level are applied from left to right.