RE: SQL Rollup Feature

["Richard Jackson" <richardjackson@xxxxxxxxxxxxxxxxxx>]

Sorry, I didn't understand the question because I didn't know about the
rollup keyword.  I was thinking that you were using the word rollup in a
general sense, not in specific keyword sense.

I don't think that the rollup part will work.  The statement:

 Select region REGION,
        territory TERRITORY,
         sum(sales_dollars) TOTAL_SALES
 From sales
 group by region, territory

will sort of work but your won't get the subtotals at region breaks or the
grand total.  I'm mulling over a possible subquery solution with union and
order by and I think that I can make it work but I think that it will be
VERY ugly - nowhere near as attractive as the Oracle syntax.

IBM prides itself on confirming to standards.  Is the rollup keyword in some
standard or is it an Oracle extension?  For what it's worth, I think that it
is kind of cool.

LATER: I just did a quick search on the IBM AS/400 web site and "rollup" or
"roll up" as SQL keywords aren't there.

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 6:00 PM
To: 'MIDRANGE-L@midrange.com'
Subject: RE: SQL Rollup Feature


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
+---

+---
| 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
+---