× 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.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.