rollup not working

UNIXNewBie

Am looking at an Oracle SQL reference book.

They have the following SQL for ROLLUP which works

SELECT 0.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,

R.NAME REGION, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

AND O.MONTH BETWEEN 1 AND 3

GROUP BY ROLLUP (O.YEAR, O.MONTH, R.NAME)
They also have the following SQL which apparently is supposed to produce a
partial ROLLUP. It does not work however. I receive ORA-00933: SQL command
not properly ended.

Why is this happening?

Note that the only difference is that O.YEAR is now outside the ROLLUP
SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,

R.NAME REGION, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

AND O.MONTH BETWEEN 1 AND 3

GROUP BY O.YEAR, O.MONTH ROLLUP (R.NAME)

Jul 19 '05 #1

sybrandb

"UNIXNewBie" <no****@nospam.com> wrote in message news:<1K********************@magma.ca>...

Am looking at an Oracle SQL reference book.

They have the following SQL for ROLLUP which works

SELECT 0.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,

R.NAME REGION, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

AND O.MONTH BETWEEN 1 AND 3

GROUP BY ROLLUP (O.YEAR, O.MONTH, R.NAME)
They also have the following SQL which apparently is supposed to produce a
partial ROLLUP. It does not work however. I receive ORA-00933: SQL command
not properly ended.

Why is this happening?

Note that the only difference is that O.YEAR is now outside the ROLLUP
SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,

R.NAME REGION, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

AND O.MONTH BETWEEN 1 AND 3

GROUP BY O.YEAR, O.MONTH ROLLUP (R.NAME)

I spot a , missing between O.MONTH and rollup

Apart from that you really need to include your version in every post.
This is an area which has been improved several times, so you might
just be using the newest syntax against an older version of the
database (or set your compatible parameter incorrectly)

Sybrand Bakker
Senior Oracle DBA

Jul 19 '05 #2

UNIXNewBie

That was it.

Thanks for the tip on the version. I'll remember that for next time.

This was Oracle 9.2
<sy******@yahoo.com> wrote in message
news:a1**************************@posting.google.c om...

"UNIXNewBie" <no****@nospam.com> wrote in message

news:<1K********************@magma.ca>...

Am looking at an Oracle SQL reference book.

They have the following SQL for ROLLUP which works

SELECT 0.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,

R.NAME REGION, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

AND O.MONTH BETWEEN 1 AND 3

GROUP BY ROLLUP (O.YEAR, O.MONTH, R.NAME)
They also have the following SQL which apparently is supposed to produce a partial ROLLUP. It does not work however. I receive ORA-00933: SQL command not properly ended.

Why is this happening?

Note that the only difference is that O.YEAR is now outside the ROLLUP
SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,

R.NAME REGION, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

AND O.MONTH BETWEEN 1 AND 3

GROUP BY O.YEAR, O.MONTH ROLLUP (R.NAME)

I spot a , missing between O.MONTH and rollup

Apart from that you really need to include your version in every post.
This is an area which has been improved several times, so you might
just be using the newest syntax against an older version of the
database (or set your compatible parameter incorrectly)

Sybrand Bakker
Senior Oracle DBA

Jul 19 '05 #3