× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




Phil,

I think what you want is the group by clause.

Select acct, inv#, sum(amt)
        from file
        group by acct, inv#
        order by acct, inv#

The order by is optional.  This will give you a summary of each acct,
inv# combination.

If you want the detail and total in a single file try something like
this (not tested):

-- Generate the summary amounts by account and inv#
With sumtable as (
        Select acct, inv#, max(amt) as lastamt, sum(amt) as totamt
                from file
                group by acct, inv#)

-- Join detail with summaries.  Substitute 0 for records not matching
the total record.
Select t1.acct, t1.inv#, t1.amt, coalesce(totamt, 0)
        from file.t1 left outer join sumtable t2 on t1.acct = t2.acct
and
        t1.inv# = t2.inv# and t1.amt = t2.lastamt
        order by t1.acct, t1.inv#, t1.amt

HTH,

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Phil Kestenbaum
Sent: Tuesday, January 16, 2007 7:00 AM
To: RPG programming on the AS400 / iSeries
Subject: Use of SQL



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.

Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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.