Search code examples
jpqlcuba-platform

Subselect in JPQL

Looking to perform a possible subselect in JPQL. I use SQL often and looking to perform a query like this:

Select ((Select SUM(e.hours.hours) from app$Time e 
where e.type = 'Purchased')
 - 
(Select SUM(f.hours.hours) from app$Time f 
where f.type = 'Used'))

I have not seen many examples of JPQL performing a query like this, hoping that this is possible.

Solution

  • Jay,

    JPA 2.1 specification doesn't support subquery in the select list.

    From the specification:

    The SELECT clause can contain one or more of the following elements: an identification variable that ranges over an abstract schema type, a single-valued path expression, a scalar expression, an aggregate expression, a constructor expression.

    Workaround: you can perform two JPQL queries and subtract the Used from the Purchased in the java code