Error message of null values

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?

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT DB2/400 and DB2 UDB When is the YES option for 'reuse deleted files' function the best choice? Allow access to data from a stored procedure result set using COBOL or RPG Access path and an open data path differences Convert a numeric physical file to a character in SQL without leading zeros Inserting data from a CTE into a file in SQL Implement variables in SQL when creating an alias Simplify the process of converting a spool file from iSeries into an Excel spreadsheet Control and fix date format problems on AS/400 SQL Disable SQL query optimizer tool that removes trailing blanks Change from a Julian date to a "true" date for a search criteria index DB2 UDB (universal databases) When is the YES option for 'reuse deleted files' function the best choice? Access path and an open data path differences Database performance comparisons on IBM i Implement variables in SQL when creating an alias Simplify the process of converting a spool file from iSeries into an Excel spreadsheet Control and fix date format problems on AS/400 SQL Disable SQL query optimizer tool that removes trailing blanks A solution to write data to QDLS in Excel format Timestamp not in valid format using CPYFRMIMPF Uniquely matching two fields in an SQL table to fields in a different table DB2 UDB (universal databases) Research

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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