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)
2  5272 
"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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Burt |
last post by:
MS has been nice enough to add the Cube and Rollup operators so I can
have totals with my results.
But out of shear meanness they won't let me use them unless I use a
Group By clause in my...
|
by: Frank Py |
last post by:
I found this great rollup query example that uses grouping and sub
totals. Would it be possible to expand on this and include a group
within a group and subtotal each group? For example, parent...
|
by: c.le_roq |
last post by:
Hello,
Using rollup I want to count the number of rows of a table
called Table1 which is LEFT JOINED with a table called Table2.
The problem is that we can have more than 1 rows in Table2 that...
|
by: js |
last post by:
I have a SQL Server 2003 stored procedure that uses "select ... group
by ... with rollup" syntax. The total of columns from the query varies
from 3 to 4. The query returns several rollup...
|
by: apattin |
last post by:
Hi all,
I need some expert advice on a ROLLUP fine point.
summary_table table has 4 columns:
file_id
primary_site
morphology
primary
|
by: Ike |
last post by:
When I use "With rollup," I am getting totals on columns which are numeric,
however, non-numeric columns are copying down to the rollup column (i.e. the
last column's non-numeric columns are being...
|
by: vanandwiz |
last post by:
Please find the code below.
SELECT
COMPANY.NAME,
COUNT(ITEM.ID)
FROM
ITEM,
COMPANY
WHERE ITEM.COMPANYID = COMPANY.ID
GROUP BY ROLLUP(COMPANY.NAME)
|
by: traceable1 |
last post by:
I installed the SQL Server 2005 SP2 update 2 rollup on my 64-bit
server and the performance has tanked!
I installed rollup 3 on some of them, but that did not seem to help.
I thought it...
|
by: UNIXNewBie |
last post by:
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)...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
