August 31, 2009 at 7:03 pm
Hello
This is my store procedure
create proc usp_Fact
@entFact smallint
as
declare @tipFact as varchar (25)
if @entFact = 1
set @tipFact= 'ods.c_factProv_cost'
else
set @tipFact= 'ods.c_factBanam_cost'
SELECT fac.c_fact_desc AS Service,
SUM(CASE WHEN ods.c_prv_cve = '005' THEN 1 ELSE 0 END) AS Prov,
@tipFact AS Cost,
SUM(CASE WHEN ods.c_prv_cve = '005' THEN 1 ELSE 0 END)* @tipFact AS Total,
FROM ds_ods AS ods left JOIN
cs_fact AS fac ON ods.c_fact_cve = fac.c_fact_cve
GROUP BY fac.c_fact_desc, @tipFact
and send me this error when I tried to save the store procedure
Each GROUP BY expression must contain at least one column that is not an outer reference.
Do you have any idea? Can I do that?
Thanks.
August 31, 2009 at 7:36 pm
The error message is self-explanatory. What is your question?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2009 at 7:51 pm
How to asign a variable in a group by?
August 31, 2009 at 8:08 pm
Well, A) you cannot do that, and B) that's not what the error message is about.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 1, 2009 at 11:17 am
Yes, I understand the message, that's because I'm trying to find another solution for my problem.
Thanks for your time.
September 1, 2009 at 11:26 am
I'd use two separate queries in different stored procedures and use the stored procedure in your original post to determine which child procedure to call based on the value of the parameter passed to the master procedure.
September 1, 2009 at 11:50 am
That's precisely what I wanted to avoid 😛 😛 2 different queries or SP to save code, now I'm going to do what you said. 😀
Thanks Lynn.
September 1, 2009 at 4:05 pm
Try a CASE statement insead?create proc usp_Fact
@entFact smallint
as
SELECT
fac.c_fact_desc AS Service,
SUM(CASE WHEN ods.c_prv_cve = '005' THEN 1 ELSE 0 END) AS Prov,
CASE
WHEN @entFact = 1 THEN ds.c_factProv_cost
ELSE ods.c_factBanam_cost
END AS Cost,
SUM(CASE WHEN ods.c_prv_cve = '005' THEN 1 ELSE 0 END)
*
CASE
WHEN @entFact = 1 THEN ds.c_factProv_cost
ELSE ods.c_factBanam_cost
END AS Total
FROM
ds_ods AS ods
LEFT JOIN
cs_fact AS fac
ON ods.c_fact_cve = fac.c_fact_cve
GROUP BY
fac.c_fact_desc,
CASE
WHEN @entFact = 1 THEN ds.c_factProv_cost
ELSE ods.c_factBanam_cost
END
September 1, 2009 at 6:41 pm
Fantastic, that worked. :w00t: Thanks Lamprey13
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply