[SQL Server] Problem with ROLLUP syntax

cms9651 · August 28, 2012, 4:02pm

Hi.

I have problem with one query with syntax ROLLUP.

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

'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:

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:

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… :x

Can you help me?
Thanks in advance…


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 · August 28, 2012, 4:40pm

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 · August 28, 2012, 4:50pm

Thanks for reply!

Rework my query?

Any suggestions?

cpradio · August 28, 2012, 4:56pm

Yeah, I know. Not the best of news.

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

	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 · August 28, 2012, 5:00pm

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/6396198/group-by-clause-with-rollup

cms9651 · August 28, 2012, 5:07pm

Thank you for reply.

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

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

cpradio · August 28, 2012, 5:15pm

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 · August 28, 2012, 5:24pm

Strange, when I ran this

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…

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

cms9651 · August 28, 2012, 5:46pm

You have right! Thanks a lot!

Now working in SQL Server 2005 and 2008 !

This version not working:

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:

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 · August 28, 2012, 7:18pm

i will give it the old college try