JPQL Subquery method

Support
#1

Currently I am using two different queries:


Select e.hours.hours from app$Time e
where e.type = 'Used'

and


Select e.hours.hours from app$Time e
where e.type = 'Purchased'

I need to subtract the used from the purchased. Is there a way that I can perform a sub query in JPQL code section?

Something along the lines of below be great but I cannot seem to figure out the proper structure.


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

Any help or hints would be appreciated!

#2

Hi Jay,

How many entities of type Purchased and Used in the database?

#3

Jay,

I found your topic on StackOverflow (http://stackoverflow.com/questions/43634165/subselect-in-jpql)
CUBA supports JPA 2.1 specification and subqueries aren’t supported 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.