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)

[sybrandb@yahoo.com]

Re: rollup not working

"UNIXNewBie " <nospan@nospam. comwrote in message news:<1KednaVlH dAXqqDdRVn-gw@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

[UNIXNewBie]

Re: rollup not working

That was it.

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

This was Oracle 9.2
<sybrandb@yahoo .comwrote in message
news:a1d154f4.0 402260040.66f41 541@posting.goo gle.com...

"UNIXNewBie " <nospan@nospam. comwrote in message

news:<1KednaVlH dAXqqDdRVn-gw@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