0 Replies Latest reply on Feb 14, 2007 1:55 PM by Mike Cev

    Newbee question about ORM?

    Mike Cev Newbie

      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.

      Native SQL:
      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);