Re: SQLRPGLE Question.

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Michael

I did a google on "with rollup" SQL

It is not a syntax found in all database products, but one article 
gave something like the following as an equivalent:

SELECT   PLAYERNO, SUM(AMOUNT)
FROM     PENALTIES
GROUP BY PLAYERNO
UNION
SELECT   CAST(NULL AS SIGNED INTEGER), SUM(AMOUNT)
FROM     PENALTIES

This is close, but with these problems:
1. No SIGNED modifier on iSeries
2. GROUP BY does not sort automatically

I got it to sort the players and put the final total at the end by 
using this (PLAYERNO is INTEGER, AMOUNT is NUMERIC(5,2))

SELECT   PLAYERNO, SUM(AMOUNT)
FROM     PENALTIES
GROUP BY PLAYERNO
UNION
SELECT   CAST(NULL AS INTEGER) PLAYERNO, SUM(AMOUNT)
FROM     PENALTIES
ORDER BY PLAYERNO

Notes:
1. You can't use just NULL in the second SELECT - needs to match data 
type of first SELECT
2. In order to use the column name in the ORDER BY, you have to use 
an alias in the second SELECT

Now to try something like yours

SELECT company, unit, area, region, sum(custcount)
FROM custdata
GROUP BY company, unit, area, region
UNION
SELECT company, cast(null as integer) unit, cast(null as integer) 
area, cast(null as integer) region, sum(custcount)
FROM custdata
GROUP BY company
ORDER BY company, unit, area, region

I'm not sure if the order is guaranteed to put the NULL at the end - 
if you want to make sure of the order, an additional sort field could 
be used - or a value for the particular field that will always be 
larger than anything you are using.

There is an alternative approach using QM queries and forms - you can 
have a GROUP BY QMQRY and use a QMFORM that itself has summary 
columns - with output to a table -

HTH
Vern

At 11:29 AM 12/26/2006, you wrote:


> I creating a web program that's going to retrieve just weekend customer
> counts.  I need a summary record by unit, area, region and then by company.
> I'm having trouble on finding examples of getting subtotals with SQL on the
> AS/400.  I've seen the "WITH ROLLUP" with mySQL but it's not supported on
> the AS/400.
>
> Sorry to bother again... anyone have any ideas?
>
>
> Ciao!
> Michael Schutte
> Classic Italian dishes with a Bob Evans Twist - Enjoy our new Homestyle
> Pastas!
>
> --
> 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.