Friday, March 30, 2012

Return COMPUTE from stored proc

I'm trying to figure out how to get my stored proc below to just return the result for COMPUTE only:
ALTER PROCEDURE [dbo].[procname]
AS
BEGIN
Select (cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR WHERE e='06'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
order by CustomerNumber
COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100)
END
When my stored proc is run, it should only return one value, the result of COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100). Right now however, it returns only the list from the select (below) which I don't want, I just want the COMPUTE value returned which is one value (the sum of the items below):
27256.000000
14218.000000
0.000000
14930.000000
54824.000000
148616.666667
73320.000000
85956.000000
105507.500000
67911.904762
55276.190476
14467.500000
5985.000000
20910.000000
118784.000000
5340.000000
5295.000000
567.500000

It is not possible to modify behavior of COMPUTE. It is a non-standard / proprietary extension so you should avoid using it. Instead write your own query using say ROLLUP/CUBE operator to get the sum at the desired levels. This will allow you to control the output rows by filtering on GROUPING function return values. See BOL for examples.|||

Thanks figured that after spending half my day trying. I found a way to sum what I needed and return one GT without compute:

Select Total = SUM(gt)

FROM

(Select DistinctCustomerName,

CustomerNumber,

FeeGoal_AZ AS FG,

FeeSchedule,

(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

from DCR WHERE branch='00002'

group by CustomerName,

CustomerNumber,

FeeGoal_AZ,

FeeSchedule

) as dTable

No comments:

Post a Comment