RE: SQL Rollup Feature

["MCPARTLAND, Stan" <stanley.mcpartland@xxxxxxxxxx>]

Richard,

I use group by without any problem.  It's the rollup (and cube) feature that
I want to try.  TipWorld's SQL tip on 7/13 discussed the rollup feature of
group by which provides subtotals and a grand total of your group by set.
I've not been able to get SQL to recognize "rollup".  I revert to the old
Query/400 when I need to do this, but I'm always looking for a way to do it
in SQL.  The following is an excerpt from the SQL Tip.

Regards,
Stan

 Select nvl(region,'TOTAL COMPANY') REGION, 
         nvl(territory, 'Total Region') TERRITORY, 
         sum(sales_dollars) TOTAL_SALES 
 From sales 
 group by rollup (region, territory); 

The results of this query would be: 
 
REGION   TERRITORY   TOTAL_SALES 
------   ---------   ------------ 
EAST        1          1500.00 
EAST        2          2000.00 
EAST   Total Region    3500.00 
WEST        3          3000.00 
WEST        4           500.00 
WEST   Total Region    3500.00 
TOTAL COMPANY          7000.00 
 
The above example uses Oracle syntax. The NVL represents Null Value 
Logic. In other words, if the total for Territory is Null, the value 
Total Territory is substituted. When Company is Null, TOTAL SALES is 
substituted. (Having reached the end of sales for all territories or 
Regions, null values will be returned.) 

-----Original Message-----
From: Richard Jackson [mailto:richardjackson@richardjackson.net]
Sent: Tuesday, July 18, 2000 4:28 PM
To: MIDRANGE-L@midrange.com
Subject: RE: SQL Rollup Feature


Send a message with a statement and the error message that you get.  I use
group by all the time on the 400 and it works for me.

Richard Jackson
mailto:richardjackson@richardjackson.net
www.richardjacksonltd.com
Voice: 1 (303) 808-8058
Fax:   1 (303) 663-4325

-----Original Message-----
From: owner-midrange-l@midrange.com
[mailto:owner-midrange-l@midrange.com]On Behalf Of MCPARTLAND, Stan
Sent: Tuesday, July 18, 2000 3:57 PM
To: 'Mailing List - MIDRANGE-L'
Subject: SQL Rollup Feature


Group by with rollup is an SQL feature that provides subtotals and totals
from an SQL query.  I assume SQL on the AS/400 does not support the Rollup
feature of the Group By clause.  I don't see anything in the SQL Reference
manual.  I have not had any luck trying variations of Oracle and SQL server
syntax.  I'm hoping I've missed something and someone knows a way to perform
a rollup on the AS/400.

Regards,
Stan McPartland
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---