RE: Use of SQL

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.