
Big IN clause cause access violation
I have a simple, but large, query that causes an access violation when run
(I have tried it in Database Explorer and Database Desktop and it causes an
access violation there too).
Does anyone know why? The database being used contains Paradox tables.
Here is the query:
SELECT
BOOK_ID
FROM
BOOK
WHERE
LOCATION_ID = 1 AND
AUTHOR_ID IN (8719, 3929, 5186, 6858, 6845, 9969, 9678, 10771, 10564,
< SNIP > < big list of numbers removed >
7453, 168, 11858, 387, 7446, 7291)
The IN clause is huge, but when I take away the line:
LOCATION_ID = 1 AND
it works fine.
When I make the list of numbers smaller then it works fine too. Is it just a
limit on the number of values you can put in the IN clause? But why then
does it work with the location line removed?
If you're wondering why I'm doing it like this it is because I found that
using ids, and then a lookup field to find the author names (in this case)
is faster than joining the tables.
Steve