I am trying to represent following SQL in HQL but getting exception about ON keyword for the LEFT JOIN is not being recognized. My xml configurations are straight mapping to each table. It seems like I have to create some type of relationship within the xml configuration and remove the ON clause from the HQL but I am not sure how that relationship should be defined with in the xml.
How else can I state that LEFT JOIN should be happening on certain columns? Here is my SQL and two hbm files that supports it.
select prptCol.program, prptCol.col_tag , prptCol.template_flag, prptClient.template_flag
from dbo.prpt_col prptCol
LEFT outer JOIN dbo.prpt_client prptClient
ON prptCol.program = prptClient.program
and prptCol.rpt_tag = prptClient.rpt_tag
and prptCol.col_tag = prptClient.col_tag
and prptClient.client = 'ABC'
where prptCol.rpt_tag = 'test'
and prptCol.program = 'testprogrem'
Code to create HQL
Query q = session .createQuery( "select " + " prptCol.program, prptCol.colTag , prptClient.templateFlag, prptCol.templateFlag " + " from PrptColumn prptCol " + " left outer join PrptClient prptClient" + " ON prptCol.program = prptClient.program" + " and prptCol.rptTag = prptClient.rptTag" + " and prptCol.colTag = prptClient.colTag" + " and prptClient.client = ? " + " where prptCol.rptTag = ? and prptCol.program = ? " ) .setString(0,a_clientName) .setString(1, a_rpt_tag) .setString(2, a_program);