RE: Use of SQL

In the absence of a unique key, RRN can be used here by substituting
'MAX(RRN(PKTEST)) as MR' in T1 for 'MAX(KEYFLD) as MK', then changing the
join from 'PKTEST.KEYFLD=T1.MK' to RRN(PKTEST) = T1.MR. The ORDER BY would
also change accordingly.

JJ



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Joe Pluta
Sent: Tuesday, January 16, 2007 9:56 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Use of SQL

Easier to do a CTE, I think.

The problem is figuring out the sequence and whether there is a key field
that is unique.  But assuming there is a unique key field that is ascending
(in this case, called KEYFLD), we could do the following:

WITH T1 AS (SELECT ACCT, INV#, MAX(KEYFLD) as MK, TOT(AMT) AS TOT
  FROM PKTEST GROUP BY ACCT, INV#)
SELECT PKTEST.ACCT, PKTEST.INV#, KEYFLD, AMT,
  COALESCE(TOT, 0)
  FROM PKTEST LEFT OUTER JOIN T1
    ON PKTEST.ACCT=T1.ACCT and PKTEST.INV# = T1.INV#
   And PKTEST.KEYFLD=T1.MK
ORDER BY ACCT, INV#, KEYFLD

The only difference in the output for this vs. Phil's original case would be
that there would be a column for the key value.  The CTE would generate one
record with the total and the maximum key value.  The LEFT OUTER JOIN gives
one row for each row in the original file, and the coalesce returns either
the total amount if there is a hit on the CTE or zero if there is not (and
there should be a hit on the last record in every tuple).

If there is no unique key value, things get a lot trickier, because it's
really hard to figure out which record should get the total and which
shouldn't.

All this being said, this is almost a picture perfect situation to use RPG,
and maybe even <shudder> the cycle.

Joe

From: vhamberg@xxxxxxxxxxx

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