Value Datasource aggregate the product of two column failure

Support
#1

Hi there,

I have a Value Datasource defined as:


<valueCollectionDatasource id="firstPiechartDs">
            <query>
                <![CDATA[select p.name, sum(p.amount) from demo$Product p  ORDER BY p.createTs]]>
            </query>
            <properties>
                <property datatype="string"
                          name="entityName"/>
                <property datatype="decimal"
                          name="numValue"/>
            </properties>
        </valueCollectionDatasource>

While this query works fine in pdAdmin:


SELECT name, SUM(qty * cost) AS inventoryCost FROM public.demo_product GROUP BY name order by inventoryCost DESC;

It failed in a web gui controller:


queryStr = "select p.name, SUM(p.qty * p.cost) AS inventoryCost from demo$Product p GROUP BY p.name order by inventoryCost DESC";

with the following error:


JpqlSyntaxException: Errors found for input jpql:[select p.name, SUM(p.qty * p.cost) AS inventoryCost from demo$Product p GROUP BY p.name order by inventoryCost DESC]
CommonErrorNode [<unexpected: [@8,18:18='(',<23>,1:18], resync=SUM(>]
CommonErrorNode [<mismatched token: [@10,20:20='.',<62>,1:20], resync=.qty * p.cost) AS inventoryCost from demo$Product p>]

Is this caused by Cuba has not implemented the aggregation of arithmetic of multiple columns yet?

Any help is much appreciated.

Thanks,
-Mike

1 Like
#2

Hi Mike,

Aggregation of arithmetic of multiple columns is not implemented in CUBA yet because we use a parser that conforms to the JPA 2.1 spec which states that aggregate function applied to a path expression. But we’ll consider supporting it in the future because EclipseLink can execute such queries.

#3

Hi Andrey,

Would you like to create a tracking for easier follow up?

Thank you,
-Mike

#4

Sure. Added the link.

#5

:ticket: See the following issue in our bug tracker:

https://youtrack.cuba-platform.com/issue/PL-8880