"Invalid in the select list" but it *is* in the group by!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

"Invalid in the select list" but it *is* in the group by!!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-11 : 18:46:28

Jennifer writes "I have been using SQL for a very long time, and have never seen this particular interaction. When I run the following query, I get the error "Fiscal_Year is invalid in the SELECT list because it is not contained in either an aggregate function or the GROUP BY clause."

However, it *is* in the GROUP BY clause.

Furthermore, if do *any one* of the following things, the query works
1. remove the subselect and make it a real table
2. change the left outer join to an inner join
3. remove the HAVING clause
4. remove the "rtrim" from the field in the GROUP By

Now, normally I would just make one of the previous changes and be done. However, this query was auto-generated by a user via Business Objects and this person doesn't know how to go in and edit the SQL. I'm loathe to change our reporting model because, as far as I can tell, this really should be valid SQL. Here I've simplified the query extremely so it only includes the relevant pieces.

SELECT
rtrim(c.Fiscal_Year)
FROM
ODS_Calendar_Master c
left outer join (select 1 colname) d on (1=1)
GROUP BY
rtrim(c.Fiscal_Year)
HAVING
max(c.Fiscal_Period) > 'FP2006-01'

What's going on here? Is this an actual SQL bug?
Here's our stats: SQL Server Enterprise Edition version 8.00.760 (SP3). Microsoft Windows NT - 5.0 (2195)

Any insight is appreciated!"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 02:43:36

I don't understand your LEFT JOIN?

LEFT JOIN (SELECT 1 ColName) d ON (1 = 1) ???

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-10-12 : 05:56:26

Where is the Aggregate clause (sum, AVG, count, etc)....as coded you might as well go for a distinct.

"Peso"...re "on (1=1)"....this is a form of cross-join...ie join on every record...when no matching values exist.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 06:00:05

Is it compulsory to include aggregate function in SELECT list if we include GROUP BY?

Harsh Athalye
India.
"Nothing is Impossible"

pootle_flump

1064 Posts

Posted - 2006-10-12 : 06:08:24

quote:
Originally posted by harsh_athalye

Is it compulsory to include aggregate function in SELECT list if we include GROUP BY?

Nope - hence you see a lot of GROUP BY in SQL "written" by people using query designers since it is usually easier to group than to use DISTINCT. However, there is a HAVING clause so it looks fair enough to me.

The join makes no sense to me either.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources