JPQL custom query in Manage BDM with SUM doesn't work

Submitted by erre.muller on Wed, 06/07/2017 - 14:21
1
0
-1

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?

jpql; SUM; GROUP BY; BDM

Comments

Submitted by Lionel Palacin on Sat, 06/10/2017 - 00:10

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

?

Permalink
3 answers

Submitted by xat on Wed, 06/21/2017 - 15:35
1
0
-1

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

Submitted by erre.muller on Fri, 06/30/2017 - 08:13

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

Permalink

Submitted by Lionel Palacin on Mon, 06/12/2017 - 20:34
1
0
-1

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

Comments

Submitted by erre.muller on Mon, 06/12/2017 - 21:18

Thank you Lionel
so the limit is in what I'm asking, two output, or in how I'm asking in my request?
For better understanding, I never can request two output or my request is not correct to have the output needed (in this case how is the correct way)?
Cheers

Permalink

Submitted by erre.muller on Mon, 06/12/2017 - 16:34
1
0
-1

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.

Notifications