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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: