CODEWITHSUNDEEP
sqlhibernatehql
Jean Logeart
Jean Logeart

Reputation: 53829

HQL count from multiple tables

I would like to query my database using a HQL query to retrieve the total number of rows having a MY_DATE greater than SOME_DATE.

So far, I have come up with a native Oracle query to get that result, but I am stuck when writing in HQL:

SELECT
    (
     SELECT COUNT(MY_DATE) 
     FROM Table1 
     WHERE MY_DATE >= TO_DATE('2011-09-07','yyyy-MM-dd') 
    ) 
    + 
    (
     SELECT COUNT(MY_DATE) 
     FROM Table2 
     WHERE MY_DATE >= TO_DATE('2011-09-07','yyyy-MM-dd')
    ) 
AS total

I actually have more than 2 tables but I keep having an IllegalArgumentException (unexpected end of subtree).

The working native Oracle basically ends with FROM dual.

What HQL query should I use to get the total number of rows I want?

Upvotes: 2

Views: 1866

Answers (1)

ChssPly76
ChssPly76

Reputation: 100726

First of, if you have a working SQL query, why not just use that instead of trying to translate it to HQL? Since you're returning a single scalar in the first place, it's not like you need anything HQL provides (e.g. dependent entities, etc...)

Secondly, do you have 'dual' mapped in Hibernate? :-) If not, how exactly are you planning on translating that?

That said, "unexpected end of subtree" error is usually caused by idiosyncrasies of Hibernate's AST parser. A commonly used workaround is to prefix the expression with '0 +':

select 0 + (
   ... nested select #1 ... 
       ) + (
   ... nested select #2 ... 
       ) as total
  from <from what exactly?>

Upvotes: 1

Related Questions