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.
Author |
Topic |
waitangi
Starting Member
1 Post |
Posted - 2009-04-25 : 07:10:24
|
I have a table with about 500 result. The table is on linked server ORACLE.
The following query: SELECT * FROM CAPEX_CODE_DICTA as a WHERE a.active = 1 AND id NOT IN ( SELECT id FROM OPENQUERY (SUN515, 'SELECT replace (trim (ANL_CODE), ''\r\n'') AS id FROM sun.INTRA_V_T09_CAPEX WHERE STATUS = 0' ) )
returns 1 row, which seems to be ok.
This query: SELECT a.id FROM CAPEX_CODE_DICTA as a WHERE a.active = 1 AND id NOT IN ( SELECT id FROM OPENQUERY (SUN515, 'SELECT replace (trim (ANL_CODE), ''\r\n'') AS id FROM sun.INTRA_V_T09_CAPEX WHERE STATUS = 0' ) ) returns all rows.... Am I missing something?. The difference between these two is that first gets *, and second gets only id column. The point is that I need id only. I have already found workarround (changing query construct) but that is not the point :-). I just want to know what is going on. Any explanation is appreciated.
Regards, waitangi |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-25 : 08:03:01
|
have a look at the query plans I would always insert into a table to separte the openquery fromm the rest of the process.
what happens if you do
select id from ( SELECT * FROM CAPEX_CODE_DICTA as a WHERE a.active = 1 AND id NOT IN ( SELECT id FROM OPENQUERY (SUN515, 'SELECT replace (trim (ANL_CODE), ''\r\n'') AS id FROM sun.INTRA_V_T09_CAPEX WHERE STATUS = 0' ) ) ) a
========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy. |
 |
|
|
|
|