# Load foundset with recursive query

**URL:** <https://forum.servoy.com/t/load-foundset-with-recursive-query/18862>\
**Category:** Classic Servoy\
**Created:** [May 20, 2016, 8:26am UTC](https://forum.servoy.com/t/load-foundset-with-recursive-query/18862 "2016-05-20T08:26:04Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![abeermann](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@abeermann](https://forum.servoy.com/u/abeermann)\
**Post date:** [May 20, 2016, 8:26am UTC](https://forum.servoy.com/t/load-foundset-with-recursive-query/18862/1 "2016-05-20T08:26:04Z")

</div>

Hello Everbody

My recursive Query to get rbkontaktids, which should be loaded into a form:

var koquery = " DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ " ;  
koquery += " (rbobjekteid) " ;  
koquery += " AS (SELECT ro.rbobjekteid " ;  
koquery += " FROM rbobjekte ro " ;  
koquery += " WHERE rbobjekteid = ? and aktivkz = ‘1’ " ;  
koquery += " UNION ALL SELECT ro.rbobjekteid FROM rbobjekte ro ,TMPOBJ " ;  
koquery += " WHERE ro.oberobjektid = TMPOBJ.rbobjekteid ) " ;  
koquery += " SELECT rbkontaktid from rbkontakt join tmpobj on rbkontakt.rbobjekteid = TMPOBJ.rbobjekteid " ;  
koquery += " where rbkontakt.rbkontaktstatus = ‘erledigt’ “;  
koquery += " order by rbkontakt.rbkontaktstatus desc, rbkontakt.geaendert desc”;

var args = new Array();  
args[0] = scopes.FM.knotenid;

This one is the old version. It works as expected but it fires two querys: (Performance !? The table rbkontakt has serveral million records!)

var c\_kontakte = databaseManager.getDataSetByQuery(‘maxdb’, koquery, args, -1);  
forms.Auswahl\_Kontakte.foundset.loadRecords(c\_kontakte);

As you allow querys beginning with “declare” this new version should fire only one query command and hopefully run much faster.

forms.Auswahl\_Kontakte.foundset.loadRecords(koquery,args);  
but i get:  
com.servoy.j2db.dataprocessing.DataException: [-5016] (at 67): Missing delimiter: )

Any help welcomed  
Best regards  
Albert

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [May 20, 2016, 11:41am UTC](https://forum.servoy.com/t/load-foundset-with-recursive-query/18862/2 "2016-05-20T11:41:31Z")

</div>

Hi Albert,

Be aware that when you use SQL directly in foundset.loadRecords() that Servoy will wrap this in another SELECT.  
I.e.

```auto
SELECT <pkColumn> FROM <tableName> WHERE <pkColumn> IN (<yourSQL>) ORDER BY <pkColumn>

```

For that reason your SQL (also CTE’s) will not work.

So you can do it the way you did, using a dataset to load into the foundset, or use an in-memory foundset to load these records in.

Hope this helps.