0

I have an openquery that I using to hit an Oracle DB with from SQL server. The query runs without error but does not return any results. If I take out the sub select and change it to where pid = and input any of the ids on the list I get results.

Any ideas on what I am doing wrong?

SELECT * FROM openquery(DBtest04, 
'SELECT * FROM DBUSER.PLOG where 
pid in (''select pid from dbtest.dbo.gsy 
'') and timeofevent > to_date(''2015-01-01 12:00'',''yyyy-mm-dd hh:mi'')  ')

If I run this query I get 239 rows returned:

    'SELECT * FROM DBUSER.PLOG where 
    pid = 990066657 and timeofevent > to_date(''2015-10-25 12:00'',''yyyy-mm-dd hh:mi'')  ')
spacerobot
  • 265
  • 1
  • 5
  • 23
  • First, are you sure the query runs without errors (as opposed to "the errors were handled, by sending them to NULL")? It turns out a lot of lazy programmers do just that. Then, are you able to run the query directly in the target database to see if it returns any results? –  Oct 31 '16 at 14:50
  • Try doing just a `SELECT * FROM DBUSER.PLOG` in the open query without the complicated Where clause to see if you get results – SS_DBA Oct 31 '16 at 15:10
  • If I do the following query I get 239 rows retruned: 'SELECT * FROM DBUSER.PLOG where pid = 990066657 and timeofevent > to_date(''2015-10-25 12:00'',''yyyy-mm-dd hh:mi'') ') – spacerobot Oct 31 '16 at 15:19

0 Answers0