valueCollectionDatasource query using "CASE WHEN..."

Support
#1

Hi,
For a valueCollectionDatasource, I am trying to sum some payments if they match a paymentType condition.
I would like to show the sum for each paymentType in a separate column, so I was thinking of using “CASE WHEN” in my query, as follows:


SELECT pmt.payDay, pmt.concertPerson.id,
SUM( CASE WHEN pmt.paymentType = 0 THEN pmt.amount ELSE 0 END),
SUM( CASE WHEN pmt.paymentType = 1 THEN pmt.amount ELSE 0 END),
SUM( CASE WHEN pmt.paymentType = 2 THEN pmt.amount ELSE 0 END),
SUM( CASE WHEN pmt.paymentType = 3 THEN pmt.amount ELSE 0 END),
SUM(pmt.amount)
FROM tojm$Payment pmt
WHERE pmt.concertPerson.concert.id = :ds$concertDs
GROUP BY pmt.payDay, pmt.concertPerson.id

This however results in JpqlSyntaxException: Errors found for input jpql.
The error is at “CASE WHEN”…

Can I use this kind of query in Cuba?
Thank you for your help,

#2

Hi Matthis,

“CASE WHEN” 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,
Thanks for your answer - understood that this is not supported currently.
Would an alternative using a native sql query to set the valueCollectionDatasource source be possible?

#4

Hi,

what i do in these cases oftentimes is just to create a SQL VIEW that will enhance the data like you did with your case. So you basically split the query into the definition of a view and the normal “WHERE” part. Additionally you create an Entity for the View that will just take the VIEW as the table name.

You’ll end up with something like this:


SELECT pmtView.payDay, pmt.concertPerson.id,
pmtView.paymentTypeZeroAmount,
pmtView.paymentTypeOneAmount,
pmtView.paymentTypeTwoAmount,
pmtView.paymentTypeThreeAmount,
pmt.amount
FROM tojm$PaymentSumView pmtView
WHERE pmt.concertPerson.concert.id = :ds$concertDs

And the logic just shifts into the view creation. In this case you are not constraint by the JPQL query anymore, because you write your create statements for the tables & views db-specific anyway (in 10-create-db.sql e.g.)…

Bye
Mario

#5

Hi Mario,

Thank you very much for your interesting reply, and apologies for the late reply.
I will try this approach!

#6

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

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