community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

SQL: IN Statement (1000 limit Oracle) and Character limit for In Clause relationship

I am using an Excel worksheet as input to get detailed information from a database for each cell.  Using the Dynamic input tool I set the character limit to 12000 based on each cell containing 10+(comma+string quote) characters.  This worked fine till the 1000 limit encountered where some cells could have less than 10 characters and some cells could have over 10 characters.

 

" Error: ORA-01795: maximum number of expressions in a list is 1000".  

 

 

The data is accessed using a read id and cannot insert into a table and then use in IN Statement.

(Select * from table_info where key in (select key from list_table) -->unlimited

vs 

(Select * from table_info where key in ('ABCDEFGHIJ','ABCDEFGHIJKL','ABC',..........)  -->String limitation and then Ora-01795.

 

The limit should be based on the Database limitation and not on number of string characters and the tool should be smart enough to build the In Statement based on the connection established.  Please fix.

1 Comment
Meteor

Based on what you are saying, I am wondering if Alteryx is assuming it is a CSV and then creates a row for each string after a comma. Also, you did not mention how many rows your Excel worksheet had. If it has over 1000 rows, then this is failing on the DB side not the Alteryx side.