Group By Error Under SQL 2005

Our stored procedure throws the following error when running on 2005
w/compatibility set to 90, but not 80:
"Each GROUP BY expression must contain at least one column that is not an
outer reference. Severity 15, State 1, Procedure "procname", line 351"
The code in question is:
....
OR
(EXISTS (SELECT e.logical_seat_row, b.logical_seat_num, count(*)
FROM #ZoomSet e
WHERE e.logical_seat_num >= b.logical_seat_num
and e.logical_seat_num <= c.logical_seat_num
and e.bit_col & 64 = 64
and e.logical_seat_row = b.logical_seat_row
GROUP BY e.logical_seat_row, b.logical_seat_num
HAVING count(*) >= @.num_wc_ind))
)
.....
I've searched support.msft.com, as well as this newsgroup, and all of the
web, but can't find this error anywhere. Any ideas would be appreciated.
Thanks!
Steven Bras
Tessitura Network, Inc.>> Our stored procedure throws the following error when running on 2005 w/co
mpatibility set to 90, but not 80:: "Each GROUP BY expression must contain a
t least one column that is not an outer reference. Severity 15, State 1, Pr
ocedure "procname", line 35
1" <<
As best I can tell from the fragment posted, the code lookd fine from a
standards viewpoint (ignoring the bit operator crap). I would clean it
up for human use (one BETWEEN is easier to read and understand than two
comparisons) and see if that helps.
Since it is an EXISTS() predicate, use the * instead of a list; my
thought is that the engine might be trying to build the list when all
it needs to do is find is one row.
OR
(EXISTS (SELECT *
FROM #ZoomSet AS E
WHERE E.logical_seat_num
BETWEEN B.logical_seat_num
AND C.logical_seat_num
AND E.bit_col & 64 = 64
AND E.logical_seat_row = B.logical_seat_row
GROUP BY E.logical_seat_row, B.logical_seat_num
HAVING COUNT (*) >= @.num_wc_ind))
)
The other things are to re-write the whole query to get rid of the
temp table and the assembly language bit fiddling.
It looks like you are trying to find a block of vacant seats on the
same row. Ihave queries for that in SQL FOR SMARTIES which are
simpler.|||Thanks; I do appreciate your response and am a long-standing admirer of your
columns and books.
But why does the error now occur under 2005 where it didn't used to under
SQL 2000?
--
Steven Bras
Tessitura Network, Inc.
"--CELKO--" wrote:

351" <<
> As best I can tell from the fragment posted, the code lookd fine from a
> standards viewpoint (ignoring the bit operator crap). I would clean it
> up for human use (one BETWEEN is easier to read and understand than two
> comparisons) and see if that helps.
> Since it is an EXISTS() predicate, use the * instead of a list; my
> thought is that the engine might be trying to build the list when all
> it needs to do is find is one row.
> OR
> (EXISTS (SELECT *
> FROM #ZoomSet AS E
> WHERE E.logical_seat_num
> BETWEEN B.logical_seat_num
> AND C.logical_seat_num
> AND E.bit_col & 64 = 64
> AND E.logical_seat_row = B.logical_seat_row
> GROUP BY E.logical_seat_row, B.logical_seat_num
> HAVING COUNT (*) >= @.num_wc_ind))
> )
> The other things are to re-write the whole query to get rid of the
> temp table and the assembly language bit fiddling.
> It looks like you are trying to find a block of vacant seats on the
> same row. Ihave queries for that in SQL FOR SMARTIES which are
> simpler.
>|||> But why does the error now occur under 2005 where it didn't used to under
> SQL 2000?
In the Books Online topic 'sp_dbcmptlevel', it states the following:
Compatibility level setting of 80 A GROUP BY clause in a subquery that
references a column from the outer query succeeds.
Compatibility level setting of 90 A GROUP BY clause in a subquery that
references a column from the outer query returns an error as per the SQL
standard.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"StevenBr" <sbras@.community.nospam> wrote in message
news:ACFBFA3A-8CAF-4875-A83F-38607B5BDC23@.microsoft.com...
> Thanks; I do appreciate your response and am a long-standing admirer of
> your
> columns and books.
> But why does the error now occur under 2005 where it didn't used to under
> SQL 2000?
> --
> Steven Bras
> Tessitura Network, Inc.
>
> "--CELKO--" wrote:
>|||n Mon, 26 Jun 2006 17:39:15 -0700, "Gail Erickson [MS]"
<gaile@.online.microsoft.com> wrote:

> In the Books Online topic 'sp_dbcmptlevel', it states the following:
>Compatibility level setting of 80 A GROUP BY clause in a subquery that
>references a column from the outer query succeeds.
>Compatibility level setting of 90 A GROUP BY clause in a subquery that
>references a column from the outer query returns an error as per the SQL
>standard.
Interesting.
It may also be worth pointing out why this would be so. Any reference
within the subquery that refers to the outer query is, for the purpose
of the subquery, a reference to a constant. And there is no reason to
include a constant in a GROUP BY list.
In the specific example posted:

>....
> OR
> (EXISTS (SELECT e.logical_seat_row, b.logical_seat_num, count(*)
> FROM #ZoomSet e
> WHERE e.logical_seat_num >= b.logical_seat_num
> and e.logical_seat_num <= c.logical_seat_num
> and e.bit_col & 64 = 64
> and e.logical_seat_row = b.logical_seat_row
> GROUP BY e.logical_seat_row, b.logical_seat_num
> HAVING count(*) >= @.num_wc_ind))
> )
>.....
it is the reference to b.logical_seat_num in the GROUP BY that is
redundant, since there can be only one value for any given evaluation
of the subquery. But we can go farther and observe that since
e.logical_seat_row = b.logical_seat_row, the reference to
e.logical_seat_row in the GROUP BY is also redundant.
Which means the entire GROUP BY clause is not required, as they
resolve to a single row, and since (as has already been pointed out)
the SELECT list for an EXISTS subquery should be *, the GROUP BY
should be redundant... BUT WAIT! Will it be legal to have a HAVING
clause reference an aggregate expression COUNT when the select list is
an *? Good question, I'm not sure.
--Quick test in 2000 and 2005, using system tables, demonstrates that
--it works!
select *
from sysobjects
where exists
(select * from syscolumns
where syscolumns.id = sysobjects.id
having count(*) > 30)
But it still looks funny to my sensitive nature. I think it might be
safer to rewrite without the EXISTS:
OR
(@.num_wc_ind <=
(SELECT count(*)
FROM #ZoomSet e
WHERE e.logical_seat_num >= b.logical_seat_num
and e.logical_seat_num <= c.logical_seat_num
and e.bit_col & 64 = 64
and e.logical_seat_row = b.logical_seat_row)
)
Roy Harvey
Beacon Falls, CT|||Hi,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.