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 last 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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close