|
Sorting can be by Acct/inv. I will look into this and the other method, thanks, Phil -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of vhamberg@xxxxxxxxxxx Sent: Tuesday, January 16, 2007 10:25 AM To: RPG programming on the AS400 / iSeries Subject: Re: Use of SQL Hi Phil I am intrigued by this one - there are a few techniques for getting running totals and subtotals - what you want is a little different - subtotals are often a separater record from the data, as when you use Query/400. With SQL, one technique is to use UNIONs - in this case, you really have 2 distinct but related recordsets that you want to combine: 1. Records in groups by ACCT & INV# that do NOT have an AMT = max(AMT) -> no subtotal 2. Records that ARE the max(AMT) record -> subtotal I've assumed char(3) for acct & inv#, decimal(7, 2) for amt. Number 1 can be done this way - there are other ways, such as an exception join with a common or nested table expression: select ACCT, INV#, AMT, decimal(0, 9, 2) TOTAMT from PKTEST O where AMT not in (select max(AMT) from PKTEST where ACCT = O.ACCT and INV# = O.INV#) Number 2 can be done this way - select ACCT, INV#, max(AMT) AMT, decimal(sum(AMT), 9, 2) TOTAMT from PKTEST group by ACCT, INV# So to get what you want, do a UNION: select ACCT, INV#, AMT, decimal(0, 9, 2) TOTAMT from PKTEST O where AMT not in (select max(AMT) from PKTEST where ACCT = O.ACCT and INV# = O.INV#) union select ACCT, INV#, max(AMT) AMT, decimal(sum(AMT), 9, 2) TOTAMT from PKTEST group by ACCT, INV# You did not specify how to sort it - your example suggests that you want it sorted by AMT with a given ACCT & INV#. To have AMT in possible arrival sequence, use order by ACCT, INV#, TOTAMT Actually the UNION seemed to force the following sort, but i don't know if that is guaranteed. To have AMT in ascending order, use order by ACCT, INV#, TOTAMT, AMT There you go - divide and conquer!! HTH Vern -------------- Original message -------------- From: "Phil Kestenbaum" <pkestenbaum@xxxxxxxxxxx>
HI, I would like to do this in an SQL statement: I want to take a record that has ACCT INV# AMT TOTAMT Where totamt is created in the SQL When there is a break, a new ACCT INV. Only when there is a new Acct/Inv then the prev. record should contain
the total of all the prev Acct/Inv Amt's in Totamt. So for example: Acct INV Amt Totamt 123 001 1.00 0.00 123 001 2.00 0.00. 123 001 3.00 6.00 123 002 1.00 0.00 Etc. Thanks, Phil This message contains information proprietary to our company. It is
intended to
be read only by the individual or entity named above or their
designee.
Any distribution of this message or the information contained herein
without
written permission from our company is strictly prohibited. If the
reader of
this message is not the intended recipient or an agent responsible for
delivering it to the intended recipient, you are hereby notified that
you have
received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If
you have
received this communication in error, please notify us immediately by
e-mail,
and delete the original message. -- 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.