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