Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Error message of null values

One user wrties, "In V5R3 I got the error message of null values not allowed in column or variable ZORD#T for SQL update." DB2 expert Kent Milligan offers some advice.

I keep getting the error message of null values not allowed in column or variable ZORD#T for SQL update (using...

V5R3):

UPDATE Z9519 A SET A.ZORD#T = (SELECT COALESCE( B.ZORD#T, A.ZORD#T ) FROM Z9517 B WHERE A.ZADES1 = B.ZADES1 AND A.WHS=B.WHS)

While I have no problem with the same tables, same SQL in V5R2 (I already set 0 to all numeric fields in table Z9519 before executing SQL update and all numeric fields in Z9517 are not NULL). Can you offer some suggestions as to why I'm having this problem?

In examining your statement, I noticed that you are not specifying selection (i.e. Where clause) on the update statement. As a result, you are requesting that every row in the Z9519 table be updated by DB2 as a result of this request. If the subselect does not produce a match for that row in Z9519, DB2 has to update to the null value (in which case the COALESCE function never even gets executed for that row, because the subselect didn't return a row).

If you want to update only a subset of the Z9519 rows (those that have a matching values for columns ZADESE1 and WHS) then you need to add that selection to the update statement:

UPDATE Z9519 A SET A.ZORD#T = (SELECT B.ZORD#T FROM Z9517 B WHERE A.ZADES1 = B.ZADES1 AND A.WHS=B.WHS) WHERE EXISTS (SELECT 'x' from Z9517 BB where A.ZADES1 = BB.ZADES1 AND A.WHS=BB.WHS)

OR

 UPDATE Z9519 A SET ZORD#T = 
  CASE 
    WHEN (SELECT B.ZORD#T FROM Z9517 B WHERE A.ZADES1 = B.ZADES1 AND A.WHS=B.WHS) IS NOT NULL 
      THEN(SELECT BB.ZORD#T FROM Z9517 BB WHERE A.ZADES1 = BB.ZADES1 AND A.WHS=BB.WHS) 
      ELSE A.ZORD#T 
   END

More on this topic

Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2 

Search400.com's targeted search engine: Get relevant information on DB2/400. 

The Best Web Links: Tips, tutorials and more.

 

This was last published in August 2005

Dig Deeper on DB2 UDB (universal databases)

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.

Please create a username to comment.

SearchDataCenter

Close