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?

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

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.