Q
Problem solve Get help with specific problems with your technologies, process and projects.

I get a -407 on the call below. Could it be the OIBRNC column in table T2?

I get a -407 on the call below. What am I missing? If I comment out the where clause I get a universal update.

UPDATE DM5TEST.OHDEBTP T1
SET OHALSG =
  (SELECT MAX(T2.OIBRNC)
   FROM DM5TEST.OIDEBTP T2
   WHERE T1.OHXRCE =
         T2.OIXRCE     AND

         T1.OHCVCD =
         T2.OICVCD     AND

         T2.OIBRNC >
         T1.OHALSG

I believe your problem is that the OIBRNC column in table T2 is not nullable - and the result of your query is NULL. The -407 return code states "AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN column-name CANNOT CONTAIN NULL VALUES".

To verify this do the following: Run a test on just the sub-SELECT statement itself without the MAX function. By this I mean, run the following SQL:
    SELECT T2.OIBRNC
    FROM DM5TEST.OIDEBTP T2, DM5TEST.OHDEBTP T1
    WHERE T1.OHXRCE =
          T2.OIXRCE     AND

          T1.OHCVCD =
          T2.OICVCD     AND

          T2.OIBRNC >
          T1.OHALSG

If I am correct your result will be a +100 SQLCODE - no rows found. You see, when a function (like MAX) is used and the WHERE clause finds no rows, the result of the function is NULL. So, you either need to make OIBRNC nullable, or change your sub-SELECT such that it cannot return a NULL using the COALESCE function. The COALESCE function returns the first argument that is not null. So, consider changing your UPDATE statement as follows:
 UPDATE DM5TEST.OHDEBTP T1
 SET OHALSG =
   (SELECT COALESCE(MAX(T2.OIBRNC), 0)
    FROM DM5TEST.OIDEBTP T2
    WHERE T1.OHXRCE =
          T2.OIXRCE     AND

          T1.OHCVCD =
          T2.OICVCD     AND

          T2.OIBRNC >
          T1.OHALSG)

Of course, you can choose another value than 0 for the second argument of the COALESCE function. Perhaps -999 would be a better choice to indicate that no rows were found. However, keep in mind that the WHERE clause found no rows, so you might have a problem if you do not want anything updated when the WHERE conditions are not met.

This was last published in April 2003

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

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

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close