HQL Query - Reporting Queries

crancran · **Joined:** Fri Nov 13, 2009 4:05 pm **Posts:** 30

I am facing a problem with using HQL and aggregate functions to generate report output in HQL. As shown in the SQL below, I am using a nested select in the FROM clause mainly due to the fact that I needed to use the CASE keyword to break the amount field into the necessary reporting buckets. From that query, the outside query performs the aggregate reporting functionality.

Code:

SELECT 
  CATEGORY_ID, 
  SITE_LOCATION_ID,
  SUM(TOTAL_AMOUNT),
  SUM(TOTAL_APPROVED),
  SUM(TOTAL_SUBMITTED),
  SUM(TOTAL_DENIED)
FROM (
  SELECT 
    d.categoryId as CATEGORY_ID,
    d.siteLocationId as SITE_LOCATION_ID,
    d.amount as TOTAL_AMOUNT,
    CASE WHEN d.status = 'APPROVED' THEN d.amount ELSE 0 END AS TOTAL_APPROVED,
    CASE WHEN d.status = 'SUBMITTED' THEN d.amount ELSE 0 END AS TOTAL_SUBMITTED,
    CASE WHEN d.status = 'DENIED' THEN d.amount ELSE 0 END AS TOTAL_DENIED
  FROM Document as d
  WHERE /* insert subcriteria */
  ) 
  GROUP BY CATEGORY_ID, SITE_LOCATION_ID

When I tried to use this I got a syntax error with the '(' following the FROM and could never get this type of query to actually execute properly. My hope was that if it worked, I could use a ResultTransformer to populate a non-entity bean with the data. I had even hoped I could do this via the Criteria API but couldn't find a solution.

Alternative #2 was to go to the database and actually create a VIEW based on the actual document table that does the internal CASE logic and breaks the amount out for the reporting aggregate functions to use, but now I would be faced with using Native SQL as this VIEW isn't defined as an entity, nor would I want it to be unless I am able to configure the entity to be a "view" and create it as a view based on a SELECT statement call in annotations.

Is there not a way to easily do this via HQL and createQuery() or using the Criteria API?
If not, is there a better approach than create the view manually and using native SQL to populate a non-entity bean?

burroinquieto · **Joined:** Sat Sep 24, 2005 11:04 pm **Posts:** 21

probably a long shot, but this might work:

Code:

select categoryId, siteLocationId,
sum(amount) as totalAmount,
sum(case when status = 'APPROVED' then amount else 0 end) as totalApproved,
sum(case when status = 'SUBMITTED' then amount else 0 end) as totalSubmitted,
sum(case when status = 'DENIED' then amount else 0 end) as totalDenied
from Document 
where blah, blah, blah... 
group by categoryId, siteLocationId

chirag.naikec · **Posted:** Wed Nov 24, 2010 2:02 am

Try this out
Note : Changes done are highlighted in bold

SELECT
Document_table.CATEGORY_ID,
Document_table.SITE_LOCATION_ID,
SUM(Document_table.TOTAL_AMOUNT),
SUM(Document_table.TOTAL_APPROVED),
SUM(Document_table.TOTAL_SUBMITTED),
SUM(Document_table.TOTAL_DENIED)
FROM (
SELECT
d.categoryId as CATEGORY_ID,
d.siteLocationId as SITE_LOCATION_ID,
d.amount as TOTAL_AMOUNT,
CASE WHEN d.status = 'APPROVED' THEN d.amount ELSE 0 END AS TOTAL_APPROVED,
CASE WHEN d.status = 'SUBMITTED' THEN d.amount ELSE 0 END AS TOTAL_SUBMITTED,
CASE WHEN d.status = 'DENIED' THEN d.amount ELSE 0 END AS TOTAL_DENIED
FROM Document as d
WHERE /* insert subcriteria */
) Document_table
GROUP BY Document_table.CATEGORY_ID, Document_table.SITE_LOCATION_ID

Give a alias name(here Document_table) to inner table you are creating and use that alias to select columns outside this inner table(for eg. Document_table.CATEGORY_ID)