I am running the following SQL statement but getting an error as "Position 1 Null values not allowed in column or variable VC_EST_PAYMENT 

  
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"

Do you know what I am doing wrong?

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)

This was first published in September 2006

Back to top