I have a guess. This could be a confusion with the between() function and the SQL between filter clause. The between in SQL expects the following syntax: between startrange AND endrange. If SQL is interpreting your between as that kind of between, it is interpreting "between(...) and number" as one condition. Then it sees "not in" and goes "huh?". This would explain why the subquery doesn't work and the big select doesn't. If you change your "between(...) and number not in..." to " fldname between startrange and endrange and number not in...", it might be all better.