[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3356?page=c... ] Chris Wilson commented on HHH-3356: ----------------------------------- Provided that you don't need to JOIN to a subselect, e.g. if you just want to further summarise the results of a query, you can use our SuperSelect custom query (http://rita.logscluster.org/browser/rita/src/org/wfp/rita/db/SuperSelect....). It has a few dependencies that you can pull out from the RITA source, or just remove. You can see some examples of its use here: http://rita.logscluster.org/browser/rita/src/org/wfp/rita/datafacade/Dash..., especially the DashboardQuery() constructor. The main process is: * Create a criteria query for the inner query (subselect) * Create a SuperSelect around it * select() some columns from the inner query in the SuperSelect, e.g. by wrapping InnerQueryExpression, NonGroupedExpression and AggregateFunctionExpression objects around some of the inner query's Criterion objects * Restrict result set rows with where() * sort() the outer result set * list() the SuperSelect to return the projected rows. The getDispatchesByDateQuery() function uses SuperSelect to build a query like this: {code:sql} SELECT (0 - SUM(ship_ltu_weight * (ship_ltu_qty / line_ltu_qty))) AS dispatch_weight, (0 - SUM(ship_ltu_volume * (ship_ltu_qty / line_ltu_qty))) AS dispatch_volume, dispatch_month FROM (SELECT SUM(dispatch.ltu_qty) AS ship_ltu_qty, line.ltu_qty AS line_ltu_qty, line.ltu_weight AS ship_ltu_weight, line.ltu_volume AS ship_ltu_volume, CAST(CONCAT(LEFT(journey.actual_dispatch_time, 7), "-01") AS DATE) AS dispatch_month FROM movement AS dispatch INNER JOIN location AS origin ON origin.location_site_id = dispatch.affected_location_site_id AND origin.id = dispatch.affected_location_id INNER JOIN site AS origin_site ON origin_site.id = origin.parent_site_id INNER JOIN request_line AS line ON line.request_site_id = dispatch.request_site_id AND line.id = dispatch.request_line_id INNER JOIN product ON product.id = line.product_id LEFT OUTER JOIN product AS family ON family.product_idnf = SUBSTR(product.product_idnf, 1, 3) INNER JOIN request ON request.request_site_id = line.request_site_id AND request.id = line.request_id INNER JOIN project ON project.id = request.project_id INNER JOIN country ON country.id = project.country_id INNER JOIN site AS request_site ON request_site.id = request.request_site_id LEFT OUTER JOIN contact AS sender ON sender.owner_site_id = request.sender_site_id AND sender.id = request.sender_contact_id LEFT OUTER JOIN org AS sender_org ON sender_org.id = sender.org_id LEFT OUTER JOIN org_type AS sender_org_type ON sender_org_type.code = sender_org.org_type_code INNER JOIN shipment ON shipment.shipment_site_id = dispatch.shipment_site_id AND shipment.id = dispatch.shipment_id INNER JOIN location AS destination ON destination.location_site_id = shipment.destination_location_site_id AND destination.id = shipment.destination_location_id INNER JOIN site AS destination_site ON destination_site.id = destination.parent_site_id INNER JOIN journey ON journey.journey_site_id = shipment.shipment_site_id AND journey.id = shipment.journey_id LEFT OUTER JOIN vehicle_category ON vehicle_category.id = journey.vehicle_category_id WHERE dispatch.is_deleted = false AND shipment.is_deleted = false AND journey.is_deleted = false AND line.is_deleted = false AND request.is_deleted = false AND project.id = $P{Project_ID} AND origin_site.id = $P{Site_ID} AND journey.actual_dispatch_time IS NOT NULL AND journey.actual_dispatch_time > "2000-01-01" AND dispatch.movement_ledger_code = "S" AND journey.journey_type_code = "T" GROUP BY line.ltu_qty, line.ltu_weight, line.ltu_volume, origin.location_site_id, origin.id, shipment.shipment_site_id, shipment.id, CAST(CONCAT(LEFT(journey.actual_dispatch_time, 7), "-01") AS DATE)) AS select_0 WHERE (ship_ltu_qty < 0) GROUP BY dispatch_month {code} It should be quite easy to add support for multiple subselects, and joining them together, to get the kind of query that Arbi Sookazian was asking for. I just haven't needed it yet, so I haven't had a reason to implement it. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira