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;