SUM and Group BY in JPQL failed

Support
#1

I am getting an error when using a sum and group by in teh SQL of datasource in XML
SQL in the XML editor for the datasource


select SUM(e.amount), c from mydb$SalesOrder e inner join e.customer c 
group by c

Error


JPQLException: 
Exception Description: Syntax error parsing [select tempEntityAlias from mydb$Customer tempEntityAlias where tempEntityAlias.id in (select SUM(e.amount), c.id from mydb$SalesOrder e inner join e.customer c group by c)]. 
[99, 118] Only one expression can be declared in a SELECT clause of a subquery.

detail of error


com.haulmont.cuba.core.global.RemoteException:
---
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [select tempEntityAlias from mydb$Customer tempEntityAlias where tempEntityAlias.id in (select SUM(e.amount), c.id from mydb$SalesOrder e inner join e.customer c group by c)]. 
[99, 118] Only one expression can be declared in a SELECT clause of a subquery.
---
org.eclipse.persistence.exceptions.JPQLException: 
Exception Description: Syntax error parsing [select tempEntityAlias from mydb$Customer tempEntityAlias where tempEntityAlias.id in (select SUM(e.amount), c.id from mydb$SalesOrder e inner join e.customer c group by c)]. 
[99, 118] Only one expression can be declared in a SELECT clause of a subquery.
2 Likes
#2

Currently you cannot use queries for aggregates and scalar values in datasources and via DataManager because the platform cannot check rights on attributes in this case. We will address this issue in future releases.
Meanwhile, create your own service on middleware and use EntityManager to load data.

#3

Hi Konstantin
Have you already planned to include this in future version and when can we expect it to be released?

#4

Hi Mortoza,
This issue boils down to two different problems:

  1. Current inability to execute queries with non-entity results via DataManager. This will be fixed in the platform release 6.3 in September 2016.
  2. Automatic mapping of arbitrary datasource query results to a dynamic non-persistent entity to display the results in UI. This problem is much more complicated and I’m not sure we will implement it for 6.3. Probably later this year.
#5

Thank you Konstantin for the update and keeping this request in plan.

#6

Scanned the changelog 6.3.0 but not sure if this has been included, any light?

#7

Hi Mortoza,
Thank you for trying out the RC.
The use of aggregate functions and non-entity attributes is still not supported in datasources and DataManager. But we have fixed the following issue: now related entities can be used in select clause like this:


select o.customer from sample$Order o

Please note that selecting scalar values like “o.amount” is still impossible. We will work on it for the next release, probably 6.4.

#8

Thank you, still a good progress made.