"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