March 13, 2013 at 2:18 pm
I get the following error: Each GROUP BY expression must contain at least one column that is not an outer reference
when I try to run this query:
INSERT INTO progsumm_dba.acc_task_sums (fiscal_year, plan_option, goal, objective_num,
task_code, account_code, person_cost, added_cost, fte_prcnt)
SELECT :fa_year, s.plan_option,s.goal, s.objective_num, s.task_code, l.account_code,
sum(s.person_cost),sum(s.added_cost), sum(ISNULL(s.fte_prcnt,0))
FROM progsumm_dba.fund_task_sums s, account_funds_lookup l
WHERE s.fiscal_year = :fa_year
AND s.plan_option = :fa_plan
AND s.fiscal_year = l.fiscal_year
AND s.plan_option = l.plan_option
AND s.fund_code = l.fund_code
GROUP BY :fa_year, s.plan_option, s.goal, s.objective_num, s.task_code, l.account_code
I have no idea what is meant by 'outer reference'. Can anyone clear this up for me?
March 13, 2013 at 2:48 pm
aheadley (3/13/2013)
I get the following error: Each GROUP BY expression must contain at least one column that is not an outer referencewhen I try to run this query:
INSERT INTO progsumm_dba.acc_task_sums (fiscal_year, plan_option, goal, objective_num,
task_code, account_code, person_cost, added_cost, fte_prcnt)
SELECT :fa_year, s.plan_option,s.goal, s.objective_num, s.task_code, l.account_code,
sum(s.person_cost),sum(s.added_cost), sum(ISNULL(s.fte_prcnt,0))
FROM progsumm_dba.fund_task_sums s, account_funds_lookup l
WHERE s.fiscal_year = :fa_year
AND s.plan_option = :fa_plan
AND s.fiscal_year = l.fiscal_year
AND s.plan_option = l.plan_option
AND s.fund_code = l.fund_code
GROUP BY :fa_year, s.plan_option, s.goal, s.objective_num, s.task_code, l.account_code
I have no idea what is meant by 'outer reference'. Can anyone clear this up for me?
I assume that :fa_year is a parameter? You can't group by something that is not a column in the base table. It looks like the column s.fiscal_year contains the same value so you could switch that out in your query and your group by.
Also, you should avoid using the old style syntax and switch to the newer join syntax.
Something like this should be close.
SELECT s.fiscal_year, s.plan_option,s.goal, s.objective_num, s.task_code, l.account_code,
sum(s.person_cost),sum(s.added_cost), sum(ISNULL(s.fte_prcnt,0))
FROM progsumm_dba.fund_task_sums s
INNER JOIN account_funds_lookup l on s.fiscal_year = l.fiscal_year AND s.plan_option = l.plan_option AND s.fund_code = l.fund_code
WHERE s.fiscal_year = fa_year
AND s.plan_option = fa_plan
GROUP BY s.fiscal_year, s.plan_option, s.goal, s.objective_num, s.task_code, l.account_code
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 2:58 pm
Thank you. Changing to the column instead of the parameter worked. Is the new join syntax standard SQL? My programs need to run against multiple databases and I don't want to change syntax if it is going to affect running elsewhere.
March 13, 2013 at 3:25 pm
aheadley (3/13/2013)
Thank you. Changing to the column instead of the parameter worked. Is the new join syntax standard SQL? My programs need to run against multiple databases and I don't want to change syntax if it is going to affect running elsewhere.
Unless you have REALLY old systems you should be fine. This is the ANSI-92 syntax. It has been around for about 20 years. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply