SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [SQL Server] Problem with ROLLUP syntax

    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;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    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
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for reply!

    Rework my query?

    Any suggestions?
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Rework my query?
    Yeah, I know. Not the best of news.

    Quote Originally Posted by cms9651 View Post
    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
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    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
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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'.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Maybe @r937 can assist. Can you provide the scheme for your tables used in this query so we might be able to recreate them?
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cms9651 View Post
    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
    Last edited by cpradio; Aug 28, 2012 at 10:35. Reason: Updated code to provide more results
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cpradio View Post
    Maybe @r937 can assist.
    i will give it the old college try
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •