Problems connecting Local table and ODBC Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Problems connecting Local table and ODBC Table

Author

Topic

TRACS
Starting Member

5 Posts

Posted - 2009-07-16 : 14:29:57

I am trying to do an inner join between three tables (2 ORACLE TABLES) and one local Table. I have created the connection manager connection for the ORACLE Serve but I can't seem to write the query refrencing the local table in the query.

SELECT ENF_SEL.NARRATIVES_T.NRR_ID, ENF_SEL.NARRATIVES_T.NRR_CIV_ID, ENF_SEL.NARRATIVES_T.NRR_INC_ID, ENF_SEL.NARRATIVES_T.NRR_CREATE_DT,
ENF_SEL.NARRATIVES_T.NRR_TITLE, ENF_SEL.NARRATIVES_LONG_T.NRR_LONG_NARRATIVE, ENF_SEL.NARRATIVES_T.NRR_UPDATE_DT
FROM ENF_SEL.NARRATIVES_LONG_T INNER JOIN
ENF_SEL.NARRATIVES_T ON ENF_SEL.NARRATIVES_LONG_T.NRR_LONG_ID = ENF_SEL.NARRATIVES_T.NRR_ID INNER JOIN [TRACS_DB].[dbo].[213UpdateApplog] ON dbo_EID_NARRATIVES.NRR_CIV_ID = [TRACS_DB].[dbo].[213UpdateApplog].SUBJECT_KEY

ORACLE TABLES:

ENF_SEL.NARRATIVES_LONG_T
dbo_EID_NARRATIVES

LOCAL TABLE:

[TRACS_DB].[dbo].[213UpdateApplog]

I have tried referencing the local table using the full path to the database on the server as well as just the table name [213UpdateApplog]

The error message I get is: "invalid table name"
I know that this is referencing the local table since the connection manager I have is the ORACLE server. I know that I am not setting up the ssis package correctly and could really use some help in explaining how to create the package with two different sources joined together. I need to use the local table to minimize the number of records that I return to only narratives from my area. This area field isn't in the ORACLE Tables. Your help is appreciated

jforward5
Starting Member

19 Posts

Posted - 2009-07-21 : 15:58:15

you might try using the with statement:

With tempTable as
(select * from oracle_tables
inner join oracle_tables)
select * from sql_table
inner join on tempTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources