3 Replies Latest reply on Nov 10, 2006 8:07 AM by mwoelke

    LEFT OUTER JOIN problem

    zengchuan

      I use jboss4.0.4RC1.
      Code:
      sql = "SELECT c.id ,c.centerId ,c.centerName ,c.centerFullName ,c.address1 ,c.address2 ,c.zipCode ,c.tel1 ,c.tel2 ,c.fax ,c.homepage ,c.parentCenterId ,c.remark ,d.centerName FROM M_Center c LEFT OUTER JOIN M_Center d ON c.parentCenterId = d.centerId ORDER BY c.levelDesc ";
      List data = manager.createQuery(sql).getResultList();

      Then problem happend:

      12:14:18,296 ERROR [PARSER] line 1:228: unexpected token: ON
      12:14:18,312 ERROR [[ZCZ0001Action]] Servlet.service() for servlet ZCZ0001Action threw exception
      javax.ejb.EJBTransactionRolledbackException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 228 [ SELECT c.id ,c.centerId ,c.centerName ,c.centerFullName ,c.address1 ,c.address2 ,c.zipCode ,c.tel1 ,c.tel2 ,c.fax ,c.homepage ,c.parentCenterId ,c.remark ,d.centerName FROM com.zeng.db.M_Center c LEFT OUTER JOIN M_Center d ON c.parentCenterId = d.centerId ORDER BY c.levelDesc ]
      at org.jboss.ejb3.tx.Ejb3TxPolicy.handleInCallerTx(Ejb3TxPolicy.java:89)
      ...
      How can I do for the sql?

        • 1. Re: LEFT OUTER JOIN problem
          mwoelke

          Try WHERE instead of ON.
          Regards, Milan Wölke

          • 2. Re: LEFT OUTER JOIN problem
            zengchuan

            When I use WHERE, new problem happened:
            20:19:38,796 ERROR [PARSER] Path expected for join!
            20:19:38,796 ERROR [PARSER] Invalid path: 'd.centerName'
            20:19:38,828 ERROR [PARSER] Invalid path: 'd.centerId'
            20:19:38,828 ERROR [PARSER] right-hand operand of a binary operator was null
            20:19:38,828 ERROR [[ZCZ0001Action]] Servlet.service() for servlet ZCZ0001Action threw exception
            javax.ejb.EJBTransactionRolledbackException: org.hibernate.hql.ast.QuerySyntaxException: Path expected for join! [ SELECT c.id ,c.centerId ,c.centerName ,c.centerFullName ,c.address1 ,c.address2 ,c.zipCode ,c.tel1 ,c.tel2 ,c.fax ,c.homepage ,c.parentCenterId ,c.remark ,d.centerName FROM com.zeng.db.M_Center c LEFT OUTER JOIN M_Center d WHERE c.parentCenterId = d.centerId ORDER BY c.levelDesc ]
            at org.jboss.ejb3.tx.Ejb3TxPolicy.handleInCallerTx(Ejb3TxPolicy.java:89)
            ...
            Please help me!

            • 3. Re: LEFT OUTER JOIN problem
              mwoelke

              Maybe this will help...

              http://www.jboss.org/index.html?
              module=bb&op=viewtopic&t=94272

              Regards, Milan Wölke