In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

SQL: IN Statement (1000 limit Oracle)

cutehappybear
7 - Meteor

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

6 REPLIES 6
Felipe_Ribeir0
16 - Nebula

Hi @cutehappybear 

 

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

 

Felipe_Ribeir0_0-1665579757251.png

 

 

 

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

 

Felipe_Ribeir0_1-1665579757339.png

 

 

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

 

Felipe_Ribeir0_2-1665579757448.png

 

 

3)Create the IN clause for each group

 

Felipe_Ribeir0_3-1665579757486.png

 

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.

 

Felipe_Ribeir0_4-1665579757271.png

 

Felipe_Ribeir0_5-1665579757246.png

 

 

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

 

Felipe_Ribeir0_0-1665579971656.png

 

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

 

Felipe_Ribeir0
16 - Nebula

Hi @cutehappybear 

 

It worked?

cutehappybear
7 - Meteor

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
7 - Meteor

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.

 

cutehappybear_3-1665914948023.png

 

 

cutehappybear_1-1665914799685.png

 

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_2-1665914888876.png

 

 

 

cutehappybear
7 - Meteor

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
16 - Nebula

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-number...

 

Thanks!

Labels
Top Solution Authors