OPENQUERY with JOIN problem

Hi all,

Cansome one help me on an openquery join syntax using a local table1 ( SQL 2005 server) with a table in Oracle that is accessible via the Openquery statement.



**What i want basically is to pull (ct.TRID,fe.RE_ID, fe.EVME) fields from anOracle table using openquery where the (ct.TRID = AEA.KEYID)...
** AEA.KEYID is a field in a local Table in a SQL2005 server.

Any help will be more than appreciated.. thanks


here is my code :


SELECT columns...
FROM
(
SELECT * FROM OPENQUERY(LINKED_ORA,
'SELECT
ct.TRID,
fe.RE_ID,
fe.EVME


FROM ORA_TABLE1@db5 fe,
ORA_TABLE2@db5 ct

WHERE

...........

')

) AS OPQ

JOIN SQLTABLE1 AEA ON AEA.KEYID= OPQ.TRID

GO

Depends on the number of rows you'll be retrieving from the Oracle database, filtering on the local KEYID values, compared to the total number of rows in the remote table.
If it's nearly the complete data, then perhaps just download the records into a local temp table (with index on KEYID), and do the join on that.
If the KEYID join will filter the data considerably, then you might download the records into a local temp tables in batches, spelling out the KEYID values from your local table in the criteria.
Problem with that in SQL 2000 and before was that you could only use a fixed query statement with OPENQUERY (no dynamic SQL). The workaround for that was to do the whole query, containing OPENQUERY, as dynamic SQL.