[SQL Server] Problem with ROLLUP syntax

**cms9651** · Aug 28, 2012 09:02

Hi.

I have problem with one query with syntax ROLLUP.

If I tried query in hosting service I have this error:

Code:

'ROLLUP' is not a recognized built-in function name.

If I tried the same query in local service it's all ok.

In hosting service the version of SQL Server is:

Code:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  
Standard Edition on Windows NT 6.1 (Build 7600: )

In local service:

Code:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation 
Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

I can not upgrade the version sql server in hosting service...

Can you help me?
Thanks in advance...

Code:

CREATE TABLE TestTable (
	[MAT] VARCHAR (100),
	[NVE] INT,
	[NVES] INT
);

INSERT INTO TestTable ([MAT], [NVE], [NVES]) SELECT
	COALESCE ([MAT], 'Total') AS [MAT],
	[NVE],
	[NVES]
FROM
	(
		SELECT DISTINCT
			CASE
		WHEN LEFT (TZZ, 2) = 'AD'
		OR LEFT (TZZ, 2) = 'AF' THEN
			'MAO'
		WHEN LEFT (TZZ, 2) = 'AG'
		OR LEFT (TZZ, 2) = 'AH' THEN
			'MAE'
		WHEN LEFT (TZZ, 2) = 'AI'
		OR LEFT (TZZ, 2) = 'AM'
		OR LEFT (TZZ, 2) = 'AS'
		OR LEFT (TZZ, 2) = 'AO' THEN
			'MAC'
		WHEN LEFT (TZZ, 2) = 'AP'
		OR LEFT (TZZ, 2) = 'AQ'
		OR LEFT (TZZ, 2) = 'AR' THEN
			'MAS'
		ELSE
			'Total'
		END AS [MAT],
		COUNT (*) AS [NVE],
		SUM (
			CASE
			WHEN (
				[R-M] = '1'
				OR [R-M] = '2'
				OR [R-M] = '3'
				OR [R-M] = '4'
			)
			AND [DATE-V] IS NOT NULL THEN
				1
			ELSE
				0
			END
		) AS [NVES]
	FROM
		dbo_40
	WHERE
		1 = 1
	AND [TZZ] NOT LIKE 'LG%'
	GROUP BY
		ROLLUP (
			CASE
			WHEN LEFT (TZZ, 2) = 'AD'
			OR LEFT (TZZ, 2) = 'AF' THEN
				'MAO'
			WHEN LEFT (TZZ, 2) = 'AG'
			OR LEFT (TZZ, 2) = 'AH' THEN
				'MAE'
			WHEN LEFT (TZZ, 2) = 'AI'
			OR LEFT (TZZ, 2) = 'AM'
			OR LEFT (TZZ, 2) = 'AS'
			OR LEFT (TZZ, 2) = 'AO' THEN
				'MAC'
			WHEN LEFT (TZZ, 2) = 'AP'
			OR LEFT (TZZ, 2) = 'AQ'
			OR LEFT (TZZ, 2) = 'AR' THEN
				'MAS'
			ELSE
				'Total'
			END
		)
	) AS SubQs
WHERE
	1 = 1
ORDER BY
	CASE MAT
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
WHEN 'MAS' THEN
	4
ELSE
	5
END;

SELECT
	[MAT],
	[NVE],
	[NVES]
FROM
	(
		SELECT
			[MAT],
			[NVE],
			[NVES]
		FROM
			TestTable
		UNION
			SELECT
				COALESCE ([MAT], 'Total') AS [MAT],
				SUM ([NVE]),
				SUM ([NVES])
			FROM
				TestTable
			GROUP BY
				(mat)
	) q
ORDER BY
	CASE MAT
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
WHEN 'MAS' THEN
	4
ELSE
	5
END;

DROP TABLE TestTable;

**cpradio** · Aug 28, 2012 09:40

It seems SQL Server 2005 does not support the ANSI/ISO version of ROLLUP, so you can't have ROLLUP(statement).
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165709

Seems like you will need to rework your query yo use the 2005 syntax for ROLLUP

**cms9651** · Aug 28, 2012 09:50

Thanks for reply!

Rework my query?

Any suggestions?

**cpradio** · Aug 28, 2012 09:56

Originally Posted by cms9651

Rework my query?

Yeah, I know. Not the best of news.

Originally Posted by cms9651

Any suggestions?

Well, I've never actually used ROLLUP so my knowledge is limited. All I know is 2005 utilizes WITH ROLLUP instead of treating ROLLUP as a function (or so it seems).

In theory, it would be similar to doing this (I think), but I'm not sure this is proper SQL, so it may need some more refinement before it executes

Code:

	GROUP BY
			CASE
			WHEN LEFT (TZZ, 2) = 'AD'
			OR LEFT (TZZ, 2) = 'AF' THEN
				'MAO'
			WHEN LEFT (TZZ, 2) = 'AG'
			OR LEFT (TZZ, 2) = 'AH' THEN
				'MAE'
			WHEN LEFT (TZZ, 2) = 'AI'
			OR LEFT (TZZ, 2) = 'AM'
			OR LEFT (TZZ, 2) = 'AS'
			OR LEFT (TZZ, 2) = 'AO' THEN
				'MAC'
			WHEN LEFT (TZZ, 2) = 'AP'
			OR LEFT (TZZ, 2) = 'AQ'
			OR LEFT (TZZ, 2) = 'AR' THEN
				'MAS'
			ELSE
				'Total'
			END WITH ROLLUP

**cpradio** · Aug 28, 2012 10:00

Also, just found this out doing a few google queries on the topic, but you may need to add a HAVING statement to perform WITH ROLLUP as described at http://stackoverflow.com/questions/6...se-with-rollup

**cms9651** · Aug 28, 2012 10:07

Thank you for reply.

I tried your last suggestion, but I have this error:

Code:

ERROR [42000][SQL Server] Incorrect syntax near the keyword 'WITH'.

**cpradio** · Aug 28, 2012 10:15

Maybe @r937 can assist. Can you provide the scheme for your tables used in this query so we might be able to recreate them?

**cpradio** · Aug 28, 2012 10:24

Originally Posted by cms9651

Thank you for reply.

I tried your last suggestion, but I have this error:

Code:

ERROR [42000][SQL Server] Incorrect syntax near the keyword 'WITH'.

Strange, when I ran this

Code:

CREATE TABLE TestTable (
	[MAT] VARCHAR (100),
	[NVE] INT,
	[NVES] INT
);

CREATE TABLE dbo_40 (
	[TZZ] VARCHAR(4),
	[R-M] VARCHAR(1),
	[DATE-V] DATETIME NULL,
);

INSERT INTO dbo_40 VALUES ('AD', '1', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AD', '1', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AD', '1', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AD', '1', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AF', '1', '2010-08-14');
INSERT INTO dbo_40 VALUES ('AD', '2', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AD', '2', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AD', '2', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AD', '2', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AF', '2', '2010-08-14');

INSERT INTO dbo_40 VALUES ('AG', '1', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AG', '1', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AG', '1', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AG', '1', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AH', '1', '2010-08-14');
INSERT INTO dbo_40 VALUES ('AG', '2', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AG', '2', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AG', '2', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AG', '2', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AH', '2', '2010-08-14');

INSERT INTO dbo_40 VALUES ('AM', '1', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AS', '1', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AO', '1', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AI', '1', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AM', '1', '2010-08-14');
INSERT INTO dbo_40 VALUES ('AS', '2', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AO', '2', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AI', '2', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AM', '2', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AS', '2', '2010-08-14');

INSERT INTO dbo_40 VALUES ('AP', '1', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AQ', '1', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AR', '1', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AP', '1', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AQ', '1', '2010-08-14');
INSERT INTO dbo_40 VALUES ('AR', '2', '2010-08-10');
INSERT INTO dbo_40 VALUES ('AP', '2', '2010-08-11');
INSERT INTO dbo_40 VALUES ('AQ', '2', '2010-08-12');
INSERT INTO dbo_40 VALUES ('AR', '2', '2010-08-13');
INSERT INTO dbo_40 VALUES ('AP', '2', '2010-08-14');

INSERT INTO dbo_40 VALUES ('LG1', '1', '2010-08-10');
INSERT INTO dbo_40 VALUES ('LG1', '1', '2010-08-11');
INSERT INTO dbo_40 VALUES ('LG1', '1', '2010-08-12');
INSERT INTO dbo_40 VALUES ('LG1', '1', '2010-08-13');
INSERT INTO dbo_40 VALUES ('LG1', '1', '2010-08-14');
INSERT INTO dbo_40 VALUES ('LG2', '2', '2010-08-10');
INSERT INTO dbo_40 VALUES ('LG2', '2', '2010-08-11');
INSERT INTO dbo_40 VALUES ('LG2', '2', '2010-08-12');
INSERT INTO dbo_40 VALUES ('LG2', '2', '2010-08-13');
INSERT INTO dbo_40 VALUES ('LG2', '2', '2010-08-14');

INSERT INTO TestTable ([MAT], [NVE], [NVES]) SELECT
	COALESCE ([MAT], 'Total') AS [MAT],
	[NVE],
	[NVES]
FROM
	(
		SELECT DISTINCT
			CASE
				WHEN LEFT (TZZ, 2) = 'AD'
				OR LEFT (TZZ, 2) = 'AF' THEN
					'MAO'
				WHEN LEFT (TZZ, 2) = 'AG'
				OR LEFT (TZZ, 2) = 'AH' THEN
					'MAE'
				WHEN LEFT (TZZ, 2) = 'AI'
				OR LEFT (TZZ, 2) = 'AM'
				OR LEFT (TZZ, 2) = 'AS'
				OR LEFT (TZZ, 2) = 'AO' THEN
					'MAC'
				WHEN LEFT (TZZ, 2) = 'AP'
				OR LEFT (TZZ, 2) = 'AQ'
				OR LEFT (TZZ, 2) = 'AR' THEN
					'MAS'
				ELSE
					'Total'
				END AS [MAT],
			COUNT (*) AS [NVE],
			SUM (
				CASE
					WHEN (
						[R-M] = '1'
						OR [R-M] = '2'
						OR [R-M] = '3'
						OR [R-M] = '4'
					)
				AND [DATE-V] IS NOT NULL THEN
					1
				ELSE
					0
				END
			) AS [NVES]
	FROM
		dbo_40
	WHERE
		1 = 1
		AND [TZZ] NOT LIKE 'LG%'
	GROUP BY
		CASE
			WHEN LEFT (TZZ, 2) = 'AD'
			OR LEFT (TZZ, 2) = 'AF' THEN
				'MAO'
			WHEN LEFT (TZZ, 2) = 'AG'
			OR LEFT (TZZ, 2) = 'AH' THEN
				'MAE'
			WHEN LEFT (TZZ, 2) = 'AI'
			OR LEFT (TZZ, 2) = 'AM'
			OR LEFT (TZZ, 2) = 'AS'
			OR LEFT (TZZ, 2) = 'AO' THEN
				'MAC'
			WHEN LEFT (TZZ, 2) = 'AP'
			OR LEFT (TZZ, 2) = 'AQ'
			OR LEFT (TZZ, 2) = 'AR' THEN
				'MAS'
			ELSE
				'Total'
		END
		WITH ROLLUP
	) AS SubQs
WHERE
	1 = 1
ORDER BY
	CASE MAT
		WHEN 'MAO' THEN
			1
		WHEN 'MAE' THEN
			2
		WHEN 'MAC' THEN
			3
		WHEN 'MAS' THEN
			4
		ELSE
			5
	END;

SELECT
	[MAT],
	[NVE],
	[NVES]
FROM
	(
		SELECT
			[MAT],
			[NVE],
			[NVES]
		FROM
			TestTable
		UNION
			SELECT
				COALESCE ([MAT], 'Total') AS [MAT],
				SUM ([NVE]),
				SUM ([NVES])
			FROM
				TestTable
			GROUP BY
				(mat)
	) q
ORDER BY
	CASE MAT
WHEN 'MAO' THEN
	1
WHEN 'MAE' THEN
	2
WHEN 'MAC' THEN
	3
WHEN 'MAS' THEN
	4
ELSE
	5
END;

DROP TABLE TestTable;
DROP TABLE dbo_40;

I received, I didn't get an SQL Server error...

Code:

MAT                                      NVE         NVES
---------------------------------------- ----------- -----------
MAO                                      10          10
MAE                                      10          10
MAC                                      10          10
MAS                                      10          10
Total                                    40          40

**cms9651** · Aug 28, 2012 10:46

You have right! Thanks a lot!

Now working in SQL Server 2005 and 2008 !

This version not working:

Code:

GROUP BY
	(
		CASE
		WHEN LEFT (TZZ, 2) = 'AD'
		OR LEFT (TZZ, 2) = 'AF' THEN
			'MAO'
		WHEN LEFT (TZZ, 2) = 'AG'
		OR LEFT (TZZ, 2) = 'AH' THEN
			'MAE'
		WHEN LEFT (TZZ, 2) = 'AI'
		OR LEFT (TZZ, 2) = 'AM'
		OR LEFT (TZZ, 2) = 'AS'
		OR LEFT (TZZ, 2) = 'AO' THEN
			'MAC'
		WHEN LEFT (TZZ, 2) = 'AP'
		OR LEFT (TZZ, 2) = 'AQ'
		OR LEFT (TZZ, 2) = 'AR' THEN
			'MAS'
		ELSE
			'Total'
		END WITH ROLLUP
	)

This working, problem with parenthesis:

Code:

GROUP BY
		CASE
		WHEN LEFT (TZZ, 2) = 'AD'
		OR LEFT (TZZ, 2) = 'AF' THEN
			'MAO'
		WHEN LEFT (TZZ, 2) = 'AG'
		OR LEFT (TZZ, 2) = 'AH' THEN
			'MAE'
		WHEN LEFT (TZZ, 2) = 'AI'
		OR LEFT (TZZ, 2) = 'AM'
		OR LEFT (TZZ, 2) = 'AS'
		OR LEFT (TZZ, 2) = 'AO' THEN
			'MAC'
		WHEN LEFT (TZZ, 2) = 'AP'
		OR LEFT (TZZ, 2) = 'AQ'
		OR LEFT (TZZ, 2) = 'AR' THEN
			'MAS'
		ELSE
			'Total'
		END WITH ROLLUP

**r937** · Aug 28, 2012 12:18

Originally Posted by cpradio

Maybe @r937 can assist.

i will give it the old college try

User Tag List

Thread: [SQL Server] Problem with ROLLUP syntax

Thread Tools

Display

[SQL Server] Problem with ROLLUP syntax

Bookmarks

Bookmarks

Posting Permissions