Free Trial

Alteryx Designer Desktop Discussions

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

filtering in db

leviatlas
5 - Atom

when using a tool Filter In-DB i discovered that my query will not run with more then 1000 entries in "FIELD" in (1,2,3,...1000). Is there a way to have a filter with more then 1000 entries? For now if I have 3000-4000 i use 4 filter nodes with 1000 each and use union after but if 10-20K entries, it will be very inefficient way.

3 REPLIES 3
DataNath
17 - Castor
17 - Castor

@leviatlas do you mean you want to put more than 1,000 arguments in an IN statement i.e. [Amount] IN (1,2,3...1000)? This is a pretty long-winded way of filtering even if possible. Could you not just use ranges instead? For example, instead of IN (1,2,3...1000)  you could use [Amount] > 0 AND [Amount] < 1001, repeated for multiple ranges. As Filter In-DB uses SQL syntax you could actually use BETWEEN as well to trim this down to [Amount] BETWEEN 1 AND 1000.

leviatlas
5 - Atom

Those are unique string values, I just gave numbers for illustration purposes. its in ('abc','def',....'ZDER')

Felipe_Ribeir0
16 - Nebula

Hi @leviatlas 

 

This same problem was solved here: Solved: Re: Workaround for SQL 1000 maximum numbers in a l... - Alteryx Community

 

You can adapt this solution to use in db components if you wish.

Labels
Top Solution Authors