Trying to make our Ingres 2.6 / OpenROAD 4.1 application be able to run on
DB2 through Ingres's gateway

One of the many problems has been UPDATE FROMs

I have a routine that parses the UPDATE FROM statement and converts it into
DB2 speak
before executing IMMEDIATE

This works well EXCEPT that DB2 returns a NULL if no rows are found due to
the inner join failing
and will then try to update the destination column with a NULL !!

e.g. if no matching owner id is found then UnderwriterID is not left alone
(as in Ingres) but updated with a null (also assuming its a nullable column)

Any suggestions, workarounds, choice remarks regarding IBM etc. very much
appreciated !

UPDATE ss03_policy_details_a t
SET ( t.UnderwriterID ) =
(  SELECT  id FROM ownr o
  WHERE t.PolicyNo = o.policy_id
  AND o.relation = 'UNWR'
  AND o.effd <= DATE('01.12.2007 00:00:00')
  AND (o.endd IS NULL OR o.endd >= DATE('01.12.2007 00:00:00'))
  AND o.id =
       (  SELECT MAX(w.id)
          FROM ownr w
          WHERE t.SystemDateTime = DATE('25.02.2008 13:14:41')
          AND t.PolicyNo = w.policy_id
          AND w.relation = 'UNWR'
          AND w.effd <= DATE('01.12.2007 00:00:00')
          AND (w.endd IS NULL OR w.endd >= DATE('01.12.2007 00:00:00'))
      )
)
WHERE t.SystemDateTime = DATE('25.02.2008 13:14:41')

--------------------------------------------------------------------------------
Colin Hay