[AccessD] ADO Recordset error near

Hi John --

According to the common usage style It would be more correct to  write  :

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

Commom rule: Be as explicit as possible when coding your T-SQL (but you can
often skip 'AS' particle for brevity)  - and you'll be safe & you'll reach
the richness.. :)

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: 18 ????? 2011 ?. 19:15
To: Access Developers discussion and problem solving; Sqlserver-Dba
Subject: [AccessD] 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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com