|
Would below work? Update DMVDRMST v Set v.AMDUE = Coalesce( (Select Sum(i.INVAM) From VAINVWK i where v.VNDNR = i.VNDNR), Cast(0.00 as Decimal(7,2)) ) - Coalesce( (Select Sum(p.PAYAM) From VAPAYWK p where v.VNDNR = p.VNDNR), Cast(0.00 as Decimal(7,2)) ) -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby Sent: Monday, September 11, 2006 3:08 PM To: 'RPG programming on the AS400 / iSeries' Subject: RE: Embedded SQL update a field based on difference of totalsin2otherfiles It doesn't work. 2 problems that I see right off: 1) On vendors with at least 1 open invoice and no unmatched payments, nothing is done to the AMDUE field. 2) Only 1 vendor actually has an unmatched payment. This vendor has 2 open invoices. The AMDUE field has been set to (the sum of the 2 invoices) less (The unmatched payment amount * 2). Ie, each open invoice is $3.75, for a total of $7.50. The unmatched payment amount is $3.00. AMDUE s/b set to $4.50, but was actually set to $1.50. The payment amount was subtracted twice, probably related to the fact there are _2_ open invoices. To confirm this, I slammed in a 3rd invoice, and then the payment amount was subtracted 3 times. I may have to give up and use record I/O. I think I've already spent too much time starting last week, but this seems like such a proper fit for SQL. -- Jeff Crosby Dilgard Frozen Foods, Inc. P.O. Box 13369 Ft. Wayne, IN 46868-3369 260-422-7531 The opinions expressed are my own and not necessarily the opinion of my company. Unless I say so.
-----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles Sent: Monday, September 11, 2006 3:26 PM To: RPG programming on the AS400 / iSeries Subject: RE: Embedded SQL update a field based on difference of totals
in2otherfiles Disregard my prior examples, posted to quick without making sure I was
sticking the statement in the right place. The should be: 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 + where z.vndnr = v.vndnr), + Cast(0.00 as Decimal(7,2)))'; Or 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 + where v.VNDNR = i.VNDNR), + Cast(0.00 as Decimal(7,2)))'; Sorry about that, Charles Wilt -- iSeries Systems Administrator / Developer Mitsubishi Electric Automotive America ph: 513-573-4343 fax: 513-398-1121-----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles Sent: Monday, September 11, 2006 3:20 PM To: RPG programming on the AS400 / iSeries Subject: RE: Embedded SQL update a field based ondifference of totalsin 2otherfiles Jeff, 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 thesubselect. I'mdoing that here with the where z.vndnr = v.vndnr. Notethat I changeto correlation name on the update file from v to z so notto confuseit 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. HTH, Charles Wilt -- iSeries Systems Administrator / Developer Mitsubishi Electric Automotive America ph: 513-573-4343 fax: 513-398-1121-----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby Sent: Monday, September 11, 2006 2:59 PM To: 'RPG programming on the AS400 / iSeries' Subject: Embedded SQL update a field based on differenceof totalsin 2 otherfiles All, I hope I'm not overstaying my welcome with too many SQLquestions,but I'm really learning a lot from it, and I can't seemto find anyexamples that fit this scenario. Or else I'm just not hitting on the right search terms. Or else you'll tell me I'm making it much harder than itneeds to be.We want to print a trial balance report "as of" a date intime. Todo this, I created QTEMP work files: the vendor masterfile and the2 related transaction files (open invoices and unmatchedpayments).Then I programmatically determined, based on dates, which transactions were "open" at that time, and inserted them into the QTEMPtransaction files.Now I want to set the QTEMP vendor master file field AMDUE (amount due) to be the net of those transactions, by vendor. Here's what I tried: ActSQL = 'Update DMVDRMST v + Set v.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)))'; Exec SQL Execute Immediate :ActSQL; Don't laugh if this is really bad SQL <g>. I dumped the program afterward. Variable ActSQL has what I expect in it, and SQLSTT is01504 (UPDATEor DELETE does not contain a WHERE clause). But AMDUE in themaster has70,662.30 dollars in _every_ record of the master. This70,662.30figure is the net of _all_ transactions for _all_ vendors. Am I heading the right direction? Thanks. -- Jeff Crosby Dilgard Frozen Foods, Inc. P.O. Box 13369 Ft. Wayne, IN 46868-3369 260-422-7531 The opinions expressed are my own and not necessarily theopinion ofmy company. Unless I say so. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting,please takea moment to review the archives at http://archive.midrange.com/rpg400-l.-- This is the RPG programming on the AS400 / iSeries(RPG400-L) mailinglist To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting,please take amoment to review the archives athttp://archive.midrange.com/rpg400-l.-- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.