QBSelect isin limit

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

QBSelect isin limit

Postby ionelioras » Thu Jun 14, 2018 3:53 pm

In servoy 7.4.9 I see that the array given for isin in a QBSelect is limited at 200 values. When the length of the array is bigger, an error occurs: QueryFunction cannot be cast.
User avatar
ionelioras
 
Posts: 37
Joined: Mon Jun 01, 2009 9:49 am
Location: Timisoara, Romania

Re: QBSelect isin limit

Postby joe26 » Wed Jun 20, 2018 12:09 am

I've used sub queries successfully in the past.

var q = databaseManager.createSelect(xxx)
q.result.add(q.column.name_column)

var r = databaseManager.createSelect(xxx)
q.result.add(q.column.other_column)
q.where.add(q.column.name_column.isin(q)

At least that's from memory... q should only return one row.
joe26
 
Posts: 172
Joined: Wed Jun 19, 2013 10:30 pm

Re: QBSelect isin limit

Postby rgansevles » Fri Jun 22, 2018 3:18 pm

The number of values is not limited to 200, with many in-values Servoy will generate a different query but will still work.
The exception you see does not occur on the latest Servoy.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: QBSelect isin limit

Postby ionelioras » Thu Jul 19, 2018 1:00 pm

rgansevles wrote:The number of values is not limited to 200, with many in-values Servoy will generate a different query but will still work.
The exception you see does not occur on the latest Servoy.

Rob


So this behavior was present in previous versions of Servoy, like 7.4.9?
When I limited the length of array to 200 it didn't throw this exception anymore. So I used an sql string for the query and it worked.
Ionel Ioras
Stb Software Development
SAN Partner

Stb Software Development - http://www.stb-development.com
User avatar
ionelioras
 
Posts: 37
Joined: Mon Jun 01, 2009 9:49 am
Location: Timisoara, Romania

Re: QBSelect isin limit

Postby ionelioras » Thu Jul 19, 2018 1:04 pm

joe26 wrote:I've used sub queries successfully in the past.

var q = databaseManager.createSelect(xxx)
q.result.add(q.column.name_column)

var r = databaseManager.createSelect(xxx)
q.result.add(q.column.other_column)
q.where.add(q.column.name_column.isin(q)

At least that's from memory... q should only return one row.


.isin can have as parameter a query or values. In my case are values as array and are more than 200.
Ionel Ioras
Stb Software Development
SAN Partner

Stb Software Development - http://www.stb-development.com
User avatar
ionelioras
 
Posts: 37
Joined: Mon Jun 01, 2009 9:49 am
Location: Timisoara, Romania

Re: QBSelect isin limit

Postby rgansevles » Fri Jul 20, 2018 11:55 am

ionelioras wrote:So this behavior was present in previous versions of Servoy, like 7.4.9?

I don't know exactly in which version, but do try the latest and greatest.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 8 guests