Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

outer reference error with group by Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 2:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 12:02 PM
Points: 2, Visits: 4
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?
Post #1430632
Posted Wednesday, March 13, 2013 2:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 12,995, Visits: 12,412
aheadley (3/13/2013)
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?


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430641
Posted Wednesday, March 13, 2013 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 12:02 PM
Points: 2, Visits: 4
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.
Post #1430647
Posted Wednesday, March 13, 2013 3:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 12,995, Visits: 12,412
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430652
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse