rollup for DB2 UDB for iSeries ? - comp.databases.ibm-db2

Message from discussion rollup for DB2 UDB for iSeries ?

jefftyzzer

View profile

More options May 16, 1:01 pm

Newsgroups: comp.databases.ibm-db2

From: jefftyzzer <jefftyz...@sbcglobal.net>

Date: Fri, 16 May 2008 10:01:29 -0700 (PDT)

Local: Fri, May 16 2008 1:01 pm

Subject: Re: rollup for DB2 UDB for iSeries ?

  Reply to author
  |
  Forward
  | Print
  | View thread
  | Show original
  | Report this message
  | Find messages by this author
  

On May 16, 8:58 am, "Pegaz" <kond...@poczta.onet.pl> wrote:

> > Not to worry if you're on an earlier version than 9.5 - it's old
> > functionality that's been around for a long time (pre v7 I think).

> > Here's one of the examples illustrating GROUP BY ROLLUP:

> > Example C3: If you use the 3 distinct columns involved in the grouping
> > sets of Example C2 and perform a ROLLUP, you can see grouping sets for
> > (WEEK,DAY_WEEK,SALES_PERSON), (WEEK, DAY_WEEK), (WEEK) and grand total.

> > SELECT WEEK(SALES_DATE) AS WEEK,
> > DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
> > SALES_PERSON, SUM(SALES) AS UNITS_SOLD
> > FROM SALES
> > WHERE WEEK(SALES_DATE) = 13
> > GROUP BY ROLLUP (WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON)
> > ORDER BY WEEK, DAY_WEEK, SALES_PERSON

> > [...]

> > Cheers,

> > Dave.

> What a pity! ROLLUP doesn't work on DB2 for iSeries. Do you know some
> replacement clause or tips for that ?

> best regards
> Pegaz.

Probably an ungainly series of UNIONS, e.g.,

SELECT fine-grained SUM for group A
UNION
SELECT next-highest level SUM for group A
UNION
SELECT highest level SUM for group A
UNION
SELECT fine-grained SUM for group B
UNION
SELECT next-highest level SUM for group B
UNION
SELECT highest level SUM for group B
.
.
.

-- Jeff

Reply to author Forward