Hi,

Am very new to Hibernate.
am using hibernate 2.0

I have this query in sql,

select ep.PRODUCT_ID, ep.product_Name, ep.available_Date,
count(distinct i.item_id) , count(distinct epc.comment_id)
from ec_products ep,
ec_Items i,
Ec_Product_Comments epc
where ep.PRODUCT_ID = i.product_id (+)
and ep.PRODUCT_ID = epc.product_id (+)
and i.item_State in ('to_be_shipped', 'shipped', 'arrived' )
GROUP BY ep.PRODUCT_ID, ep.product_Name, ep.available_Date
order by product_Name;

which i would like to convert to an hql query. The following is the hql query that i converted.

select ep.id, ep.productName, ep.availableDate, count(distinct i.id) ,
count(distinct epc.id)
from org.xyz.module.ecommerce.entity.BaseProduct as ep,
org.xyz.module.ecommerce.entity.Item as i,
org.xyz.module.ecommerce.entity.EcProductComments as
epc
where ep.id = i.product.id
and ep.id = epc.product.id
and i.itemState in ('to_be_shipped', 'shipped', 'arrived' )

GROUP BY ep.id, ep.productName, ep.availableDate ")

If I put a "(+)" at ep.id = i.product.id (+)
and ep.id = i.product.id (+)
It returns Old style of query.

Then i tried the other way,

ep.id = (+) i.product.id
and ep.id = (+) i.product.id

This returns the error as "java.sql.SQLException: ORA-00936: missing expression"

I dont know to use ansi convention.

Please help me out,

Regards,
Arunkumar Sreedharan

It's probably telling you to use "left outer join" or "right outer join" instead of the (+) in the syntax.