Hi,
I am trying to run a search where the like expression has lots of items in it. For example,
OCR1 like '"Mike Hunter" "George Bush" "Ronald Reagan" ........'
There are around 120 names like this in the list above. I got the message
At line 191: 'more than 100 terms/sets' not allowed in query
when I run this query. But it comes back with results.
Is it truncating the list? Why?
Is this a hard ceiling for the number of terms/sets? if not how can we change it?
What are you actually trying to accomplish that you have over 100 terms in one query? If you're doing @0 maybe you want (Mike Hunter,George Bush,Ronald Reagan,...) instead.
Hi,
The query I am trying is this,
There are some 120 names. Each has to be searched not as it is but different variations like
Mike, Hunter Mike R Hunter Mike, R Hunter ..
So, I did a REX as /Mike=,?\space=Hunter=
and I put these REX-ed names in a @0 like
'"/Mike=,?\space=Hunter=" "/George=,\space=Bush=" ...@0'
Now, can I put these REX-ed names in a comma list and not have the max terms problem?
Also, there is a 8K limit for the sql statements, right? is it different ?
is there any workaround to the size of the query?
because when I add more names the error that the sql statement is large is given.
thanks
You should be using parameters, not placing queries directly into SQL statements. Use
<$q="some query">
<sql "select * from sometable where somefield like $q">
not
<sql "select * from sometable where somefield like 'some query'">
Then you won't hit a sql limit and won't subject to excessive parse time or having to escape the special characters or let end users take over your SQL statements.
You could split the query into several chunks and use SQL "or":
<$q1="abc def">
<$q2="ghi jkl">
<sql "select ... where field like $q1 or field like $q2">
