with rollup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

with rollup

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-22 : 07:47:22

Davsy writes "I am trying to use the aggregate function "with rollup".
I've copied an example from a book, it uses the Northwind database

CREATE VIEW Vbase_cube
AS SELECT CustomerID, YEAR(OrderDate) AS Order_Year, COUNT(*) AS Order_Count
FROM Orders
WHERE (CustomerID LIKE 'A%')
GROUP BY CustomerID, YEAR(OrderDate) WITH cube

If I run the select without "CREATE VIEW Vbase_cube AS" it works fine,
Or If I run the Create View, leaving out the "with Cube" it creates the view just fine.

If I run as is, I get the following error:

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'WITHcube'.
---------------------------
OK Help
---------------------------

OS: Window XP Pro, service pack 2

SQL sever version:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Any suggestions?

Thanks"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-22 : 07:49:03

The WITH CUBE or WITH ROLLUP comes with GROUPING.
Read about SELECT.. GROUP BY statements in Books Online.

Peter Larsson
Helsingborg, Sweden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-22 : 14:35:16

The problem has nothing to do with what has been stated so far... the problem is that you must've fat fingered the "WITH CUBE" hint... take a closer look at the error you are getting and you'll notice...

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'WITHcube'.
---------------------------
OK Help
---------------------------

...you typed "WITHcube" instead of "WITH cube"...

--Jeff Moden

davsy
Starting Member

7 Posts

Posted - 2006-12-27 : 09:10:27

quote:
Originally posted by Jeff Moden

The problem has nothing to do with what has been stated so far... the problem is that you must've fat fingered the "WITH CUBE" hint... take a closer look at the error you are getting and you'll notice...

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'WITHcube'.
---------------------------
OK Help
---------------------------

...you typed "WITHcube" instead of "WITH cube"...

--Jeff Moden

davsy
Starting Member

7 Posts

Posted - 2006-12-27 : 09:14:37

Thank you Jeff,
However, I did see that - and thought the same thing.
But I actually did NOT fat finger "with cube".
I tested this many times ;)

davsy

davsy
Starting Member

7 Posts

Posted - 2006-12-27 : 09:30:13

Thank you for your response.

I have looked this up in Books online.
I can get a simple select statement using WITH CUBE or WITH ROLLUP to work just fine.
However, when I try to create a view (as instructed in a different book) using this SELECT statement, that is when I get an error.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-27 : 09:36:06

What is the error you are getting? Post the view definition here.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-27 : 09:53:47

From the error message, it seems that you missed the space between WITH and CUBE keyword. The view definition which you posted works just fine in the northwind database.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

davsy
Starting Member

7 Posts

Posted - 2006-12-27 : 09:59:52

thank you - but I did not miss the space.
not sure why the error msg shows the error like that.

I actually keyed in the select with "WITH CUBE" and ran it just fine. Then I added CREATE VIEW as... to the top of the select without changing the SELECT statement and got the error.
I also added tried adding many, many spaces between WITH and CUBE just to be sure and still got the error.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-27 : 10:04:41

I ran the exact view definition you posted and it got created fine. You must be missing something...in your case, does the view gets created?

try

exec sp_helptext 'Vbase_cube'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

davsy
Starting Member

7 Posts

Posted - 2006-12-27 : 10:10:04

You ran:
CREATE VIEW Vbase_cube
AS SELECT CustomerID, YEAR(OrderDate) AS Order_Year, COUNT(*) AS Order_Count
FROM Orders
WHERE (CustomerID LIKE 'A%')
GROUP BY CustomerID, YEAR(OrderDate) WITH cube

(INCLUDING the WITH CUBE) and it worked?
so why does it NOT work for me?

I have NO idea what to do next - what version SQL server are you using?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-27 : 10:20:34

Yes, exactly same definition...I don't know if this is the problem with the edition/service pack you have.

My SQL Server info is:

Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

davsy
Starting Member

7 Posts

Posted - 2006-12-27 : 10:46:08

Any idea what the problem could be related to?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources