Strange query result - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Strange query result

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources