SQL Server: COMPUTE clause... pointless? - Tilted Forum Project Discussion Community

Stompy · 08-31-2004, 10:54 AM

I'm selecting a result set from SQL Server from a dynamically built stored proc that has n columns (created by a function during runtime).

I need to sum up the data from specific numeric columns, so I'm using the COMPUTE clause which returns a separate set of data w/ all the summed up columns.

The problem is... Microsoft's developers are seriously retarded. Why? Because I found out from various Google searches that you CAN NOT alias the columns returned by COMPUTE!! This, essentially, renders any COMPUTE uses other than a BASIC "select * from ... " query totally useless.

Why?

It names all your fields "sum" if doing a sum ("avg" if doing averages, etc..). For this one set of data, I have 15 columns returned in this compute, all are named 'sum'. SQL Server complains if you try to alias these fields.

This wouldn't be a problem if I made my columns static, but they aren't... and the result set has to now be accessed via column indexes, which is impossible.

The stored proc I'm calling from is dynamic in that there are n amount of columns based on user defined data. On each of those nth field numeric columns, I need to have a compute clause ran. However, where my columns are called things like "Sales" or "Downloads" (defined by user), there's no way I can reference their counter part in the compute by without writing a function to strip away all non-numeric fields and keep an array of which name has what index in the computed result set. I'd like to name the COMPUTE result set for a "Sales" column "TotalSales" .. and for a "Download" column "TotalDownload".. that way when I loop through the result set (since I don't know how many fields it contains), I can reference a specific totals column like

...
int curTotal = row[i]["Total" + column].Value;
...

All of this is insanely ridiculous. There's nothing in the help files about aliasing these tables, and Like I said, even reading through various posts on google groups has revealed that you can't alias these columns (not to mention that trying to do a google search on "sql server compute clause alias" returns pretty much nothing CLOSE to what I'm trying to find out).

An excerpt from a summary about COMPUTE:

Quote:

The COMPUTE clause cannot be used with INTO and cannot contain aliases
for column names, although aliases can be used in the select_list.
The COMPUTE keyword can be used without BY to generate grand totals,
grand counts, and so on. The ORDER BY clause is optional only if you
use the COMPUTE keyword without BY.

Sorry, but that's crazy. There's absolutely no reason not to let us alias those columns.

Short of selecting everything from the initial set into a temp table, then immediately calling a "select * from #temp" (to get my first result set) and "select sum(col1), Sum(col2)... from #temp", is there any way to do this?

Wow, I didn't think it would be hard to do something so common...

magua · 09-03-2004, 06:40 PM

COMPUTE is for backwards compatibility. Perhaps ROLLUP will do what you want? Instead of returning the data in a seperate resultset, it'll be part of the normal resultset -- you can identify the ROLLUP rows by checking GROUPING(<field you're grouping by>) = 1. ROLLUP will only work as long as you're not wanting to group any calculations -- it doesn't like that too much, and if that's your intention, you're probably just best off writing your own extra query to do so.

Here's an example from the Northwind db:

Code:

SELECT CASE WHEN GROUPING(CustomerID) = 1 THEN 'ALL' ELSE CustomerID END AS CustomerID, CASE WHEN GROUPING(LastName) = 1 THEN 'ALL' ELSE LastName END AS Employee, COUNT(*)
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY CustomerID, LastName WITH ROLLUP

09-03-2004, 06:40 PM	#2 (permalink)
magua Tilted	COMPUTE is for backwards compatibility. Perhaps ROLLUP will do what you want? Instead of returning the data in a seperate resultset, it'll be part of the normal resultset -- you can identify the ROLLUP rows by checking GROUPING(<field you're grouping by>) = 1. ROLLUP will only work as long as you're not wanting to group any calculations -- it doesn't like that too much, and if that's your intention, you're probably just best off writing your own extra query to do so. Here's an example from the Northwind db: Code: SELECT CASE WHEN GROUPING(CustomerID) = 1 THEN 'ALL' ELSE CustomerID END AS CustomerID, CASE WHEN GROUPING(LastName) = 1 THEN 'ALL' ELSE LastName END AS Employee, COUNT(*) FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID GROUP BY CustomerID, LastName WITH ROLLUP