Workaround for SQL 1000 maximum numbers in a list

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!

Datasets

Accepted answers

Felipe_Ribeir0

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.

Workaround for SQL 1000.yxmd

All comments

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

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

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.

Workaround for SQL 1000.yxmd

VarunLakhwani

I faced the same problem today and was able to work around it using your suggested solution. Thank you so much!

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235