July 27, 2010 at 9:54 am
I have the following query:
select * from table1
where col1 in (select col1 from table2 where col2 = 123)
The query returns results and no error.
However, the following returns an error:
select col1 from table2 where col2 = 123
Msg 207, Level 16, State 1, Line 1
Invalid column name 'col1'.
Any ideas what's going on here?
The subquery is wrong, but that seems to be ignored by the main query...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 27, 2010 at 10:26 am
select * from table1
where col1 in (select col1 from table2 where col2 = 123)
This is actually a correlated subquery and it works because col1 is valid column in table1. When you run the query by itself, col1 is not a valid column thus you get an error.
Your query is actually sort of worthless because it is like saying: select * from table1 where col1 = col1 but I assume this is just an example of a real query you are having issues with.
The probability of survival is inversely proportional to the angle of arrival.
July 27, 2010 at 11:08 am
Marios Philippopoulos (7/27/2010)
I have the following query:
select * from table1
where col1 in (select col1 from table2 where col2 = 123)
The query returns results and no error.
Because it's a perfectly valid query. It actually means this:
select * from table1
where table1.col1 in (select table1.col1 from table2 where col2 = 123)
All tables referenced in the outer query are visible in the inner query, so in this case, col1 is a valid and visible column. This is one good reason to prefix columns with table names. This will give an error:
select * from table1
where table1.col1 in (select table2.col1 from table2 where table2.col2 = 123)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2010 at 12:17 pm
Thank you both, that makes sense.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply