# Can't sort 200+ records

**URL:** <https://forum.servoy.com/t/cant-sort-200-records/13264>\
**Category:** Classic Servoy\
**Created:** [December 3, 2010, 2:24am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264 "2010-12-03T02:24:06Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![maria](https://avatars.discourse-cdn.com/v4/letter/m/34f0e0/32.png) [@maria](https://forum.servoy.com/u/maria)\
**Post date:** [December 3, 2010, 2:24am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264/1 "2010-12-03T02:24:06Z")

</div>

Hi Guys,

I’m trying to sort a foundset when it has more than 200 records and I come up with an exception saying: com.servoy.j2db.dataprocessing.DataException: Can’t find column: SERVCARDID\_1342555544 in table TEMP\_1342555542.  
I never had table TEMP\_whatevernumbers. Also SERVCARDID\_78967896786 does not exist, it’s just ‘servcardid’ really.  
Supposedly Servoy builds internal queries of its own and executes them, I’m fine with that, of course.  
But I cannot run foundset.sort(condition); and automatic sort via clicking on the table header throws the same exception in the server log.

```auto
2010-12-03 13:17 http-8080-7 ERROR com.servoy.j2db.util.Debug Throwable
com.servoy.j2db.dataprocessing.DataException: Can't find column: SERVCARDID_1342555610 in table TEMP_1342555608
     at com.servoy.j2db.dblayer.Zb.Za(Zb.java:189)
     at com.servoy.j2db.persistence.Server.translateSQLException(Server.java:663)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:933)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:982)
     at com.servoy.j2db.dataprocessing.FoundSet.sort(FoundSet.java:1252)
     at com.servoy.j2db.server.headlessclient.dataui.Zzh.Za(Zzh.java:22)
     at com.servoy.j2db.server.headlessclient.dataui.Zvf.onClick(Zvf.java:41)
     at com.servoy.j2db.server.headlessclient.dataui.Zud.onEvent(Zud.java:6)
     at org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
     at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:286)
     at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:119)
     at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
     at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1250)
     at org.apache.wicket.RequestCycle.step(RequestCycle.java:1329)
     at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1428)
     at org.apache.wicket.RequestCycle.request(RequestCycle.java:545)
     at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:479)
     at com.servoy.j2db.server.servlets.Zk.doGet(Zk.java:4)
     at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Unknown Source)

2010-12-03 13:17 http-8080-7 ERROR com.servoy.j2db.util.Debug insert into TEMP_1342555608 (SERVCARDID_1342555610) values (?) parameters: [20001 ,type: java.lang.Long]
java.sql.SQLException: Can't find column: SERVCARDID_1342555610 in table TEMP_1342555608
     at com.hxtt.global.SQLState.SQLException(Unknown Source)
     at com.hxtt.sql.di.a(Unknown Source)
     at com.hxtt.sql.bp.a(Unknown Source)
     at com.hxtt.sql.bp.a(Unknown Source)
     at com.hxtt.sql.ah.a(Unknown Source)
     at com.hxtt.sql.dk.case(Unknown Source)
     at com.hxtt.sql.dk.executeUpdate(Unknown Source)
     at sun.reflect.GeneratedMethodAccessor688.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
     at java.lang.reflect.Method.invoke(Unknown Source)
     at com.servoy.j2db.persistence.datasource.Zf.invoke(Zf.java:1)
     at $Proxy1.executeUpdate(Unknown Source)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
     at com.servoy.j2db.dataprocessing.SQLEngine.performUpdate(SQLEngine.java:143)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:352)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:982)
     at com.servoy.j2db.dataprocessing.FoundSet.sort(FoundSet.java:1252)
     at com.servoy.j2db.server.headlessclient.dataui.Zzh.Za(Zzh.java:22)
     at com.servoy.j2db.server.headlessclient.dataui.Zvf.onClick(Zvf.java:41)
     at com.servoy.j2db.server.headlessclient.dataui.Zud.onEvent(Zud.java:6)
     at org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
     at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:286)
     at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:119)
     at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
     at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1250)
     at org.apache.wicket.RequestCycle.step(RequestCycle.java:1329)
     at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1428)
     at org.apache.wicket.RequestCycle.request(RequestCycle.java:545)
     at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:479)
     at com.servoy.j2db.server.servlets.Zk.doGet(Zk.java:4)
     at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Unknown Source)

```

Sorting with less than 200 records is just fine.

I tried to work around it and load records with my own query: ‘SELECT servcardid FROM servcard WHERE servcardid IN (1,2,3,…6000)’ with or without ‘ORDER BY customerid desc’  
It sort of loads the records but it’s always only 201 record and I can’t get any more even by databaseManager.getFoundSetCount(foundset).  
In fact the last command returns 11 which is bs.

Cheers,  
Maria

---

<div class="post-metadata">

**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\
**Post date:** [December 3, 2010, 10:41am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264/2 "2010-12-03T10:41:02Z")

</div>

Maria,

When you load a foundset by pk dataset that has more than 200 rows, Servoy will not generate a 'where pk in (?, …, ?) clause, but instead the pks will be stored in a temp table and it will be used in a join.  
The SERVCARDID\_1342555610 column is just an alias we generate to make sure column names are unique.

The sorting works transparently with the temp table, it is also cleaned up afterwards.

One limitation exists, if you use databasemanager.getSQL(foundset), this will contain a ref to the temp table, but when you run it later, the temp table is gone.

Do you use this method?

Rob

---

<div class="post-metadata">

**Author:** ![maria](https://avatars.discourse-cdn.com/v4/letter/m/34f0e0/32.png) [@maria](https://forum.servoy.com/u/maria)\
**Post date:** [December 6, 2010, 12:39am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264/3 "2010-12-06T00:39:29Z")

</div>

> rgansevles:  
> Maria,
> 
> When you load a foundset by pk dataset that has more than 200 rows, Servoy will not generate a 'where pk in (?, …, ?) clause, but instead the pks will be stored in a temp table and it will be used in a join.  
> The SERVCARDID\_1342555610 column is just an alias we generate to make sure column names are unique.
> 
> The sorting works transparently with the temp table, it is also cleaned up afterwards.
> 
> One limitation exists, if you use databasemanager.getSQL(foundset), this will contain a ref to the temp table, but when you run it later, the temp table is gone.
> 
> Do you use this method?
> 
> Rob

Hi Rob,

I used this method to debug the problem and that’s where I found that temp table. Normally I don’t execute this method.  
Sean suggested that I make a case already which I will do asap.

Cheers,  
Maria

---

<div class="post-metadata">

**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\
**Post date:** [December 6, 2010, 7:52am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264/4 "2010-12-06T07:52:20Z")

</div>

Maria,

Please attach a small sample solution to your case, so we can investigate.

Rob

---

<div class="post-metadata">

**Author:** ![maria](https://avatars.discourse-cdn.com/v4/letter/m/34f0e0/32.png) [@maria](https://forum.servoy.com/u/maria)\
**Post date:** [January 27, 2011, 2:33am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264/5 "2011-01-27T02:33:23Z")

</div>

> rgansevles:  
> Maria,
> 
> Please attach a small sample solution to your case, so we can investigate.
> 
> Rob

Ok, Rob.  
Please find the sample solution attached.  
It’s not only about sorting but also loading records in the foundset.  
It has three buttons to load the foundset with a query, dataset and loadAllRecords();  
The query is most basic: select all rows from the form table.  
Pay attention that the dataset and loadAllRecords() work find and the databaseManager.getFoundSetCount(foundset) displays the correct number of records.  
However if records are loaded with a query then the foundset count is 11. It’s always 11, even if I have a different data in my application.  
Also, loading records with a dataset does not work in my real solution, though it looks ok in the test solution attached.

The other thing that doesn’t work is when you click Sort right after loading records with a dataset. See full error details in console.

Please help, guys, this problem has been there forever.

Cheers,  
Maria

---

<div class="post-metadata">

**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\
**Post date:** [January 28, 2011, 7:31am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264/6 "2011-01-28T07:31:19Z")

</div>

Maria,

Please create a case in our support system with the sample attached so we can prioritize.

Thanks,  
Rob

---

<div class="post-metadata">

**Author:** ![maria](https://avatars.discourse-cdn.com/v4/letter/m/34f0e0/32.png) [@maria](https://forum.servoy.com/u/maria)\
**Post date:** [February 1, 2011, 3:18am UTC](https://forum.servoy.com/t/cant-sort-200-records/13264/7 "2011-02-01T03:18:18Z")

</div>

> rgansevles:  
> Maria,
> 
> Please create a case in our support system with the sample attached so we can prioritize.
> 
> Thanks,  
> Rob

Case ID 353089.

Cheers,  
Maria