Q

SQL statement error

DB2 expert Kent Milligan explains why this Search400.com member is receiving an error , "Position 1 Null values not allowed in column or variable VC_EST_PAYMENT. "

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

Dig deeper on iSeries SQL commands and statements

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchEnterpriseLinux

SearchDataCenter

Close