Servoy Forum

Hello Everybody

I collect some records from our SAP System and put them into an inMemoryTable (SAP_Op)
Now i want to do some overdue analysis.

The query i would fire against our SAP System would be something like this:

SELECT customerno,customername,
sum(case when overduedays <= 0 then amount else 0 end) as no_overdue,
sum(case when overduedays > 0 then amount else 0 end) as overdue,
case when overduedays > 40 then amount else 0 end) as critical_overdue,
from bsid
group by customerno,customername
order by overduedays desc

I don't kow, how to do this with querybuilder against my inMemoryTable 'SAP_Op'

Any help welcomed. Example would be great.

Best recards
Albert

I don't think you can query against an in memory table. Its not a full featured sqldatabase.
You can use the query you describe to populate an in memory table directly from SAP.

That SQL query will not work as there seems to lack one "sum(" in fourth line.
Maybe that is the problem.

When the problem is you can not query an inMemoryTable, then you could create a temporary "real" database table.
When you have to use an inMemoryTable and that can't be queried, you can iterate over it and calculate all needed values that way.

Hello Bernd

The missing "sum(" was just a typing error in my post.

I need to query the inMemoryTable.
'normal' querys work perfect

var nfqbs = datasources.mem.SAP_Op.createSelect();
nfqbs.result.add(nfqbs.getColumn("kundennr"));
nfqbs.result.add(nfqbs.getColumn("wrbtr").sum);
nfqbs.where.add(nfqbs.columns.faelligtage.ge(0));
nfqbs.groupBy.add(nfqbs.getColumn("kundennr"));
var nftreffer = databaseManager.getDataSetByQuery(nfqbs, -1);

I don't know, wether the 'case when' stuff can be done with the query builder .

Regards
Albert