I want to use a select query as a table instead of actual table name.
The documentation does not talk about using select queries in the "from" clause. Please advise.

Here is my example.

This is actual sql query.

SELECT
pr.prod_no,
sum_of_amt,
prod_desc,
prod_nam,
prod_max_ord_qty
FROM
( SELECT pr.prod_no,
SUM(ol.ord_ln_shp_amt) sum_of_amt
FROM
ord_ln ol, ORD_LN_STS os, PROD pr
WHERE
pr.prod_no = ol.prod_no
AND os.ord_ln_sts_typ_cde = 'R'
AND os.ord_no = ol.ord_no
GROUP
BY pr.prod_no ) PS ,PROD PR
WHERE
PS.prod_no = PR.prod_no

I attempted to write the above query in HQL as follows
and I get an error SQL command not ended properly.

select pr.id, pr.description, pr.name
from ( select pr.id, sum(orderLine.shipAmount)
from org.ets.k12.generic.domain.OrderLine orderLine,
org.ets.k12.generic.domain.Product pr,
org.ets.k12.generic.domain.OrderLineStatus os
where
pr.id = orderLine.product.id
and os.statusType.primaryKey = 'R'
and os.orderLine.order.id = orderLine.order.id
group by pr.id) PS , org.ets.k12.generic.domain.Product pr
where PS.prodNo = pr.id

The query generated by hibernate is given below :-

SELECT
product1_.PROD_NO AS x0_0_,
SUM(orderlin0_.ORD_LN_SHP_AMT) AS x1_0_
FROM
ORD_LN orderlin0_,
PROD product1_,
ORD_LN_STS orderlin2_,
ORD_LN orderlin3_
WHERE
(product1_.PROD_NO=orderlin0_.PROD_NO )AND
(orderlin2_.ORD_LN_STS_TYP_CDE='R' )AND
(orderlin3_.ORD_NO=orderlin0_.ORD_NO AND
orderlin2_.ORD_LN_NO=orderlin3_.ORD_LN_NO)
GROUP BY product1_.PROD_NO)PS , org.ets.k12.GENERIC.domain.Product product1_.PROD_NO WHERE PS.prodNo=product1_.PROD_NO

I appreciate your help!

Thanks,
Usha

This is not possible, sorry. Its one of those situations where its Very difficult to calculate type information in the query parser.