By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,504 Members | 1,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,504 IT Pros & Developers. It's quick & easy.

rollup not working

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
"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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.