SQL: IN Statement (1000 limit Oracle)

Hello all,

I have a Dynamic Input with a query like this:

SELECT blabla FROM blabla WHERE blabla IN ('123', '234', '495', '4823',...)

The ('123', '234', '495', '4823',...) comes from a previous variable.

However I get an error "Error: ORA-01795: maximum number of expressions in a list is 1000", because this list of values has more than 1000 elements. Sometimes it can even has 2000 or 3000, it depends because it is a dynamic list.

Is there any way in which I can split it dynamically in smaller lists of <1000 elements and do the query (dynamic input) for each one of them?

Thank you all

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

Workaround for SQL 1000.yxmd

All comments

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

Workaround for SQL 1000.yxmd

Felipe_Ribeir0

Hi @cutehappybear

It worked?

cutehappybear

Hello Felipe,

Sorry it took me quite a while to get back to you.

I really appreciate your answer.

However, I am doing exactly the same as you and the Dynamic Input tool is only querying the first group of 999 elements.

I feel like after the creation of the groups, there should be several Dynamic Input tools, but I don't see how that can be achieved.

cutehappybear

Hello again @Felipe_Ribeir0 , I guess I know why I am having this problem but I still don't know how to fix it.

My dynamic input tool receives information from a join tool, because in my query I am replacing 2 values by 2 groups of information that come from a Join tool.

And the join output is the following - and I think that it should have 1 row for each group of 999, even if the information of ATWRT, ATFVL, etc, would just repeat itself.

But I don't know how to get this output to have 4 rows (in this case, because I have 4 groups of 999, but i could have less or more).

cutehappybear

Hello once again @Felipe_Ribeir0 , sorry for all the spam.

Instead of the join tool, I used an append tool and now it is working. Thank you very much!!!!

Felipe_Ribeir0

Hi @cutehappybear , i am glad it worked!

If you could mark this post as solved too, so when people research for this topic they know that the response is useful, i would be very happy

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Workaround-for-SQL-1000-maximum-numbers-in-a-list/m-p/1014780#M250776

Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703