JPQL custom query in Manage BDM with SUM doesn't work
Hello everyone
in Bonita 7.5 I'm triyng to run this custom query
SELECT SUM(s.quantity), s.clientName
FROM ServiceRequest s
GROUP BY s.clientName
but without any answer from the engine. I tried to print also tha json variables but with no answer. Every default query works good.
In H2 work great this one
SELECT SUM(quantity), clientName
FROM ServiceRequest
GROUP BY clientName
I was wrong, I know, but where?
Hi,
I don't think aggregate queries may be used in Bonita. Looks like query must return the business object (or list of these objects) for which you create it. In your case it must return either SocialServiceRequest or list of SocialServiceRequest. I didn't find the way to run any query in which result type differ from business object type.
BR,
Roman
Comments
Hi Roman
ok, but then how can I create a simply table to show in a form the information needed? For example, I need to show that Mr Been in 2017 asked 5 hours of services ( sum of all hours in his requests, 1+2+2) , Mrs Brown asked 13 hours of services (sum of all hours in her requests, 1+3+4+5) and so on?
Thank you in advance
Hi,
I see, you can have actually only one output from the request. So in your case, you could either return SUM(s.quantity) or s.clientName but no both at the same time.
Also depends on what you choose, make sure the return type declared correspond to what you actually returns.
Cheers
Hi
thank you for your attention.
Exactly in H2 console this query return what I looking for
SELECT SUM(quantity), clientName FROM ServiceRequest GROUP BY clientName
the problem is inside this one,
SELECT SUM(s.quantity), s.clientName FROM ServiceRequest s GROUP BY s.clientName
that is the custom query wrote into Business Data Model. I haven't any result from it
../API/bdm/businessData/com.company.model.ServiceRequest?q=findMyQuery&p=0&c=10
If I use any other default query in BDM, for example, findByClientName
SELECT s
FROM SocialServiceRequest s
WHERE s.clientName= :clientName
ORDER BY s.persistenceId
it works good.
Thank you in advance for you help.
Comments
Hi,
Trying to understand.
You mean that query returns a result: SELECT SUM(quantity), clientName FROM ServiceRequest GROUP BY clientName
But this one doesn't: SELECT SUM(s.quantity), s.clientName FROM ServiceRequest s GROUP BY s.clientName
?