I am not able to find how to write this type of query in HQL

select a.x, b.y from
(select x,ab from tab1 where xxxxxx) as a and
(select y.ab from tab2 where xxxxxx) as b
where a.ab = b.ab;

Can anyone let me know how to write it or any link to verify for it.

Could you give real query please? I wounder if you need nested query at all.

cheers

Sorry, I cant give you the exact query, but trust me it is required since the second query is not just a simple select it consists of sum's and counts from another table

it is exactly something like

select a.x, b.y from
(select x,ab from tab1 where xxxxxx) as a and
(select sum(ab),sum(ac) as y from tab2 where xxxxxx) as b
where a.ab = b.ab;

first inner query is a select statement where as the second query is a calculated query.

at last could you describe ralations in xxxx if there are any
cheers

This is a smaller version of the actual query

I think this will help you get what i am trying to say.

SELECT TS.ROUTE_QTY,0, RR.INCR_STOPS_QTY
FROM
(SELECT VTSR.ROUTE_QTY, VRTS.VRPS_RUN_TS_OID_NBR
FROM VRPS_RUN_STATUS VRS, VRPS_RUN_TIME_SEGMENT VRTS,
VRPS_TIME_SEGMENT_RESULT VTSR
WHERE
VRTS.VRPS_RUN_OID_NBR = VRS.VRPS_RUN_OID_NBR
AND VRTS.VRPS_RUN_TS_OID_NBR = VTSR.VRPS_RUN_TS_OID_NBR (+)
AND VRS.DAY_PLAN_OID_NBR = :dayPlanID
) TS,
(SELECT VRTS.VRPS_RUN_TS_OID_NBR AS VRPS_RUN_TS_OID_NBR,
SUM(VRLT.INCR_STOPS_QTY) AS INCR_STOPS_QTY
FROM VRPS_RUN_STATUS VRS, VRPS_RUN_TIME_SEGMENT VRTS, VRPS_ROUTE_LEVEL_RESULT VRLT
WHERE VRTS.VRPS_RUN_OID_NBR = VRS.VRPS_RUN_OID_NBR
AND VRTS.VRPS_RUN_TS_OID_NBR = VRLT.VRPS_RUN_TS_OID_NBR
AND VRS.DAY_PLAN_OID_NBR = :dayPlanID
AND VRS.VRPS_RUN_TYPE_NBR = :executionType
GROUP BY VRTS.VRPS_RUN_TS_OID_NBR
) RR WHERE TS.VRPS_RUN_TS_OID_NBR = RR.VRPS_RUN_TS_OID_NBR (+)

regards

Did you find a solution to this problem?

No,
I changed the query and hbm files to enable inner joins so that this type of query need not be used.

I exactly don't remember the exact steps :)