Workaround for SQL 1000 maximum numbers in a list

[cutehappybear]

Hello everyone,

I have a variable that goes like this ('123', '234', '845', '39429',...) etc.

I use that variable in a Dynamic Input, to do something like:

SELECT (blablabla) FROM (blablabla) WHERE (blablabla) IN ('123', '234', '845', '39429',...).

The problem is that my list contains more than 1000 numbers, and therefore I get an error like:

Dynamic Input: Error: OraOLEDB: ORA-01795: maximum number of expressions in a list is 1000; OraOLEDB: ORA-01795: maximum number of expressions in a list is 1000 in Query...

Is there anyway in which I can split my list (which has more than 3000 records, but in the future it could even have 5000 or more), in several lists of less than 1000?

And then do several queries like

select field1, field2, field3 from table1 
where  name in ('value1', 'value2', ..., 'value999') 
    or name in ('value1000', ..., 'value1999') 
    or ...;

Thank you very much!

[fmvizcaino]

Hi @cutehappybear ,

Yes, and weirdly enough, I needed to do the same thing due to an issue I don't really recall. 😁

You could use a tile tool to separate our dataset in groups of 1000 values and it would look something like this.

Best,

Fernando Vizcaino

[Felipe_Ribeir0 · Accepted solution]

Hi @cutehappybear 

I had a similar problem here, and i solved it like this (workflow attached):

1)Have a table with the values to populate the IN clause

2)Create groups of 999 (or the amount that you want) rows each

3)Create the IN clause for each group

4)Properly configure the Dynamic Input Tool to run the query with each group of ID`s. This is done by fixing a random value at the query (here i used 999), and replacing this value with the column with the IN values previously created.

As you can see here, the queries were executed dynamically:

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.