[dba-SQLServer] ADO Recordset error near

The following statement fails in SQL Server with "error near WHERE.


SELECT * FROM ( SELECT tmmPersonInterests.* FROM tmmPersonInterests  ) WHERE PEINT_IDPE = 1

I modified it to add the table name.

SELECT * FROM ( SELECT tmmPersonInterests.* FROM tmmPersonInterests  ) WHERE tmmPersonInterests 
.PEINT_IDPE = 1

It doesn't even compile in SQL Server.

PEINT_IDPE is a field in the table and is type int.

The following compiles and runs in sql server

SELECT tmmPersonInterests.* FROM tmmPersonInterests  WHERE tmmPersonInterests.PEINT_IDPE = 1

I am trying to programmatically wrap a sql statement inside of the outer statement so that I can 
have any valid statement in the inner sql statement and as long as the inner statement exposes the 
FK, I can filter the result set to only a specific set of records.

When I hover over PEINT_IDPE it says that is not a valid column name.  I thought that SQL server 
would evaluate the inside statement, discover that PEINT_IDPE existed in tmmPersonInterests and go.

If (back in access) I just cut and paste this statement into a query window, it evaluates and pulls 
a result set.  It does give me a swearword as the alias for the interior sql statement (in QBE in 
Access).

Taking that as a clue, I changed the statement to

SELECT * FROM ( SELECT tmmPersonInterests.* FROM tmmPersonInterests  ) AS T1 WHERE PEINT_IDPE = 1

And it compiles and runs in SQL Server.

-- 
John W. Colby
www.ColbyConsulting.com