0

The following query gave me this error:

-901 Dynamic SQL Error SQL error code = -901 Implementation limit exceeded Too many values (more than 1500) in member list to match against

SELECT 
 A, B, C
FROM 
  SampleTable 
WHERE A in ( 15806,15809,22069,22135.....)

I inserted more than 1500 values on this in clause, hence, the error. I found a solution by replacing "in" to "similar to" as the following:

SELECT 
 A, B, C
FROM 
  SampleTable 
WHERE A similar to ('15806|15809|22069|22135.....')

It seems to work, I can inject more than 1500 values. But this doesn't feel right, can someone confirm this is a viable solution to the error above? The ids are in a string list, so nothing I can do to change those values. I looked at the similar to documentation of Firebird and it doesn't mention anything about inserting it in where clause with ids as the way I'm doing it.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Tonathiu Redrovan
  • 101
  • 1
  • 13
  • 1
    Let me rephrase my previous comment: what exactly is your concern or problem with the solution? It works, so it is a viable solution, so the answer to your question is "yes", which isn't very helpful and I think it doesn't address your actual concern which you haven't laid out. Personally, I would question why you even have a list of 1500+ of IDs to compare against, and wonder if there could be a better solution. – Mark Rotteveel May 24 '22 at 09:08
  • no, i was just curiuos for future errors, in case an id is null for instance..or other areas i am not familiar with similar to option. Indeed you are right, a better query is the way to avoid injection of many ids, thanks Mark. – Tonathiu Redrovan May 24 '22 at 09:54
  • What `similar to` does is a regular expression match against the left-hand value. It will convert the value of `A` to a string if it isn't a string value. As with most SQL expressions, a comparison against null will result in null. – Mark Rotteveel May 24 '22 at 09:58
  • The next problem you'll encounter is the string literal limit. Consider using of a temporary table and join. – user13964273 May 24 '22 at 10:57
  • You perhaps could swap `A` and the string - see here: https://stackoverflow.com/a/13121470/976391 and for a more complex example - `Route 2` at https://stackoverflow.com/a/43997801/976391 – Arioch 'The May 28 '22 at 17:39
  • Does this answer your question? [Filter sql based on C# List instead of a filter table](https://stackoverflow.com/questions/43997516/filter-sql-based-on-c-sharp-list-instead-of-a-filter-table) – Arioch 'The May 28 '22 at 17:46

1 Answers1

0

The clause in is limited to 1500 items. You can use join to join multiple selects.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Germán Balbi
  • 56
  • 1
  • 7