have you checked whether one of your "non nummeric" ids is inside the date range you specified...?
BETWEEN '19-DEC-14' AND '25-DEC-14';
I brought all the distinct corporate_id from remote table and created a table with the same column in my local. and then ran the same query against my local table and it works as expected. i.e., it is throwing invalid number error whether I use "IN" or "NOT IN".
Remote DB : 11g
Local DB : 9i
I followed the same exercise on the remote DB by creating a table over there with only column corporate_id which holds all the distinct corporate_id from the original table. here also it worked as expected
i.e., it is throwing invalid number error whether I use "IN" or "NOT IN".
The issue is only when I access the original table over the DBLINK in which case it throws invalid number error when I use "IN" and doesn't throw any error when I use "NOT IN".
I thank everyone for responding and appreciate your effort in educating the questioners like me.
The issue is one of its kind and only exists in our development environment. same code works good in production though.
Thank you again
Did u identify the issue... If not , provide your sample data...
You've been given most of the answer by chris227 and roger.
The final piece is that the optimizer chooses the order of evaluation for filter predicates - and this is a piece of information that will show up in the execution plan in the Predicate section.
The choice is based on the number of times a predicate will have to be evaluated and the cost of each evaluation.
I suspect you would see the IN predicate evaluated before the date predicate when the query fails, and the date predicate evaluated before the NOT IN predicate when the query succeeds.
The optimizer probably has a very low estimate of the number of rows returned by the IN predicate, therefore evalutes the expensive date predicates later, crashing when comparing an alpha with a numeric. But the optimizer probably assumes the almost all the data matches the NOT IN predicate and decides that the date predicates will eliminate much more data more efficiently, so evaluates the date predicates in the optimum order, conveniently (and luckily) eliminating any data that would crash on the alpha vs. numeric comparison.