0

I am trying to build an SQL query with around 100.000 given values.

For example:

Given values = [1, 2, 4, 5, 30, ...]

And I want to select all elements with an ID matching one of the elements.

I tried it like this:

SELECT x FROM y WHERE
  someOtherColumn = 'test'
  AND (
   id = 1
   OR id = 2
   OR id = 4
   OR id = 5
   OR id = 30
   -- ... 
 );

And like this:

SELECT x FROM y WHERE
  someOtherColumn = 'test'
  AND (
   id IN (1, 2, 4, 5, 30, ...) --- 1000 values per IN clause
   OR id IN (...)
   -- ... 
 );

Both give me the same Error:

ORA-00913: Zu viele Werte

Is there another way to do this?

This is not a 1000 Limit in IN clause issue!

MauriceNino
  • 6,214
  • 1
  • 23
  • 60
  • The error doesn't seem to be anything to do with the IDs or the IN list limit. Do you still get it if you remove the `AND ( ... )` completely? It's more likely to be in a part of a query you haven't shown, like a subquery that returns multiple values. – Alex Poole Aug 27 '20 at 14:25
  • No I don't get the same error if I remove the AND part completely. The query is working fine otherwise. @AlexPoole – MauriceNino Sep 01 '20 at 08:41

1 Answers1

0

Where do the values for your IN clause originate? Load your values into a reference table, then select that in your IN clause.

create ref_table (ref_value varchar2(20);
insert into ref_table ('1');
insert into ref_table ('2');

SELECT x FROM y WHERE
  someOtherColumn = 'test'
  AND (
   id IN (select ref_value from ref_table);
EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • They are from some webservice I call. Do you think performance will suffer hard, when putting in 100k rows with a single column? Also, `IN` allows a maximum of 1000 values - does this not apply here? – MauriceNino Aug 27 '20 at 13:12
  • Performance? How is your performance now, with a statement that throws an error? Get it to actually work. THEN you can worry about performance. The 1000 element limit only applies for a hard-coded list. – EdStevens Aug 27 '20 at 13:16
  • Okay thanks for the information. I still worry about performance before I get it to work though, because I already have it working. – MauriceNino Sep 01 '20 at 08:40