Special query aginst inmemory table

Questions and answers regarding general SQL and backend databases

Special query aginst inmemory table

Postby abeermann » Mon Jun 12, 2017 8:32 am

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
abeermann
 
Posts: 106
Joined: Fri Nov 26, 2010 12:46 pm

Re: Special query aginst inmemory table

Postby rvanderburg » Mon Jun 12, 2017 8:50 am

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.
rvanderburg
Site Admin
 
Posts: 78
Joined: Wed May 04, 2011 10:28 am

Re: Special query aginst inmemory table

Postby Bernd.N » Mon Jun 12, 2017 10:05 am

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.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Special query aginst inmemory table

Postby abeermann » Mon Jun 12, 2017 12:38 pm

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
abeermann
 
Posts: 106
Joined: Fri Nov 26, 2010 12:46 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests