EXPERT RESPONSE
This is a fairly common mistake when coding an UPDATE statement with a subselect. What is likely happening is that there is no matching row in table B (UARVCDFT) for the condition B.PLANTYPE = A.PLANTYPE AND B.PRODUCT = A.PRODSOLD. In this case the IFNULL function never even gets executed for that row, because the subselect didn't return a row and DB2 has to set the column to the null value. You need to make sure that a matching row exists for the inner table.
Try this:
UPDATE UARSLSDET AS A SET (VC_EST_PAYMENT, FEE) =
(SELECT IFNULL(EST_PAYMENT*100,0), IFNULL(FEE*100,0)
FROM UARVCDFT AS B
WHERE B.PLANTYPE = A.PLANTYPE AND B.PRODUCT = A.PRODSOLD)
WHERE A.THIRD_PARTY <> '0' AND VC_EST_PAYMENT = -1"
AND EXISTS (SELECT 'x' FROM B
WHERE B.PLANTYPE = A.PLANTYPE AND B.PRODUCT = A.PRODSOLD)
|
