Posted by Anne on 10/24/07 14:42

Hello!

Here is the statement in question:
--STATEMENT A
SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM
dbo.sourceTable)

The problem with Statement A is that 'colX' does not exist in
'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'.
Breaking the statement down, we have:

--STATEMENT B
SELECT colX FROM dbo.sourceTable
...which returns the following error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.

--STATEMENT C
SELECT colX FROM dbo.myTable
....which returns results,


If we modify Statement A to use a join:
--STATEMENT D
SELECT myTable.*
FROM dbo.myTable
JOIN dbo.sourceTable ON sourceTable.colX=myTable.colX
....we get the error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'colX'.


Any idea what SQL Server is doing in Statement A?!?!

Thanks!
Anne

• Next in forum: Access 97/SQL 2000 Problem
• Prev in forum: Re: Reusing a generated column to avoid over processing
• Thread view: Subquery with invalid column name runs

[Reply to this message]