Each GROUP BY expression must contain at least one column that is not an outer reference

Question

I have a query below that is returning the error above. Any help is greatly appreciated
@ubid is passed in to the stored proc and its an int

create table #process (processid int);

insert into #process (processid)
select id from TableB where filename like '%\%' + @reconID + '\' + convert(varchar,@ubid) + '\%' order by id asc

insert into tableA(ubid, page, code,  date, field)
select @ubid, page, code, date, field 
FROM tableA
WHERE processid in (select processid from #process) 
GROUP by @ubid, page, code, date, field 
ORDER by id asc

Answer 1

This script runs without error for me:

DROP TABLE IF EXISTS TableB
DROP TABLE IF EXISTS tableA
DROP TABLE IF EXISTS #process
go
CREATE TABLE TableB(id int, filename sysname)
CREATE TABLE tableA(id int, ubid int, page int, code int, date date, field int, processid int)
go
DECLARE @ubid int,
        @reconID varchar(12)

 create table #process (processid int);

     insert into #process (processid)
     select id from TableB where filename like '%\%' + @reconID + '\' + convert(varchar,@ubid) + '\%' order by id asc

     insert into tableA(ubid, page, code,  date, field)
     select @ubid, page, code, date, field 
     FROM tableA
     WHERE processid in (select processid from #process) 
     --GROUP by @ubid, page, code, date, field 
     ORDER by id asc

I get the same error message as you, if I include the GROUP BY.

The error message is due to the variable, which has no place in the GROUP BY clause. However, there is no point in the GROUP BY clause at all, since you are not aggregating on anything. And only removing the varible will give you a new error due to the ORDER BY clause. Which also is kind of pointless, since you are inserting the data into a table.

Answer 2

Thanks that did it. I actually commented out the GROUP by but was in the wrong DB.