Reference Local Table Inside Openquery Statement?

[Moebius01]

I'm trying to build a query to pull data from a linked Oracle server using data from a local table as part of the criteria. Is this possible? The idea I'm working on looks like this:

select * from 
openquery([linkedserver], 'select * from table where RecID IN (select RecID from local-table)')

Obviously, as is the query tries to find local-table on the Oracle server instead of locally.

 

[hneal98]

qualify your local table with the database name like this:

databasename..local_table

 

[Moebius01]

I tried with the full qualified name, but still get invalid table when it tries to prepare the openquery statement. Also tried with brackets around the name to no avail.

 

[hneal98]

is the invalid table with the local table or the one from Oracle?

Truthfully, I have never had to work with a linked server, so I don't know how that syntax goes.

 

[vongrunt]

AFAIK that won't work... openquery() must hold only Oracle stuff. Then you perform IN() check on returned results (similar to derived table).

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Moebius01]

It's the local table that causes the invalid. Basically, I'm trying to populate an IN statement with data from the local table. If I just hardcode the values, it runs fine. It's trying to pull the data from the local table in the middle of the openquery statement that triggers the error.

I'm not sure if this is even possible, but figured I'd give it a try. My next option is to pull the data into a comma seperated list and populate a variable with it, then use that variable in the query. That gets even more complex though as I have to build the statement as a variable and account for all the quotes. Not to mention I'm not exactly sure how to select the data into a comma seperated list.

 

[TLeaders]

Moebius01

My suggestion would be don't use openquery.If you actually have created a linked server you might not need it.

If you can create linked server something like this

exec sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\test',null, 'Text'

Then write an insert something like this:

insert into LABPASS (OPER,SCREENNAME,PRINTER,TERM,TODAY_DT)
SELECT 
CAST(TXT.OPER AS char (10)),
CAST(TXT.SCREENNAME AS char (20)),
CAST(TXT.PRINTER AS char (20)),
CAST(TXT.TERM AS char (20)),
CAST(TXT.TODAY_DT AS char (8))
 from txtsrv...LABPASS#txt TXT
  LEFT OUTER join LABPASS LAB ON
   LAB.OPER = CAST(TXT.OPER AS char (10)) AND
   LAB.SCREENNAME = CAST(TXT.SCREENNAME AS char (5)) AND
   LAB.PRINTER = CAST(TXT.PRINTER AS char (20)) AND
   LAB.TERM = CAST(TXT.TERM AS char (20)) AND
   LAB.TODAY_DT=CAST(TXT.TODAY_DT AS char (8)) 
WHERE LAB.OPER IS NULL

Note the 4 part name "txtsrv...LABPASS#txt".

I use this process to import new (and only new) records from a text file into sql. I have used a similar process to move selected records from sql to db2, so I have high hopes that it would work with Oracle.

Good luck
Tom

 

[mwolf00]

SELECT * 
FROM openQuery(linkedDB, 'SELECT * FROM myTable') linkTab
JOIN localTable locTab ON locTab.recID= linkTab.recID

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook