NOT-IN problem

[Analyst2004]

I am trying to run a query

select id, subid, brand from xyz
where code = 'A'
and (id,subid ) NOT IN
(select substr(z_id,1,3) , substr(z_id,4,3)
from ABC
where model = 'P')

My problem here is that the "NOT IN"
does not work as expected and returns all rows which meet
the condition from XYZ table and returns row which are in Query2 - from ABC table(Instead for NOT returing them due to NOT IN clause).

However, if I try to filter the second query - ABC table
by adding a condition (eg. select substr(z_id,1,3) , substr(z_id,4,3)
from ABC where model = 'P' and z_id like '5%')
which will make the output list from ABC small,
the Entire query returns correct result.
Is there a limitation on how many records can IN /NOT IN - query from ??
Please give some suggestions

 

[SantaMufasa]

Analyst,

Seems strange, but there could be some sort of unadvertised volumetric problem with the "IN" operation. But to test, here is a much better, much faster query that will produce the correct results for you and definitely does not suffer from volumetric issues:

select id, subid, brand from xyz
where code = 'A'
and not exists
 (select 'x'
  from abc
  where substr(z_id,1,3) = id
    and substr(z_id,4,3) = subid
    and model = 'P');

Please let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:36 (06Feb04) GMT, 11:36 (06Feb04) Mountain Time)