RE: Embedded SQL update a field based on difference of totals in 2otherfiles

Try this:

ActSQL = 'Update DMVDRMST z +
                 Set z.AMDUE = +
                 Coalesce((Select Sum(i.INVAM) - Sum(p.PAYAM) +
                           From DMVDRMST v +
                             Left Outer Join +
                           VAINVWK i On v.VNDNR = i.VNDNR +
                             Left Outer Join +
                           VAPAYWK p On v.VNDNR = p.VNDNR), +
                           Cast(0.00 as Decimal(7,2))) +
                           where z.vndnr = v.vndnr';

You have to tie a record from the updated file to the 
subselect.  I'm doing that here with the where z.vndnr = 
v.vndnr.  Note that I change to correlation name on the 
update file from v to z so not to confuse it with the 
DMVDRMST file in the subselect.

This would be a simpler form:
ActSQL = 'Update DMVDRMST v +
                 Set v.AMDUE = +
                 Coalesce((Select Sum(i.INVAM) - Sum(p.PAYAM) +
                           from VAINVWK i +
                             Left Outer Join +
                           VAPAYWK p On v.VNDNR = p.VNDNR), +
                           Cast(0.00 as Decimal(7,2)))
                           where v.VNDNR = i.VNDNR';

You shouldn't(?) need the DMVDRMST file in there twice.

Regarding the simpler one, what about the situation where there are no
transactions for a vendor?  AMDUE should be set to zero.  Since there's no
"Left Outer Join" from DMVDRMST to anything, won't some records be
untouched?