1 Reply Latest reply on Feb 12, 2006 9:52 AM by Emmanuel Bernard

    UNION and EJB-QL

    Ralf Alt Newbie

      Hallo,

      I'm using this query.
      String query = "SELECT f " +
      "FROM " + Function.class.getName() + " f, " +
      FunctionUser.class.getName() + " fu, " +
      FunctionApplication.class.getName() + " fa " +
      "WHERE f.id = fu.function.id " +
      " AND f.id = fa.function.id " +
      " AND fa.application.id = :_appid " +
      " AND fu.user.id = :_userid " +
      " UNION " +
      " SELECT f " +
      "FROM " + Function.class.getName() + " f, " +
      FunctionRole.class.getName() + " fr, " +
      FunctionApplication.class.getName() + " fa, " +
      User.class.getName() + " u " +
      "WHERE f.id = fr.function.id " +
      " AND f.id = fa.function.id " +
      " AND fr.role.id IN elements (u.roles) " +
      " AND fa.application.id = :_appid " +
      " AND u.id = :_userid ";

      Query ejbq = entityMgr.createQuery(query)
      .setParameter("_appid", applicationid)
      .setParameter("_userid", userid);

      return ejbq.getResultList();

      If I check the sql output I don't see the union.
      exec sp_executesql N'select function0_.FUNKTIONID as FUNKTIONID509_, function0_.FUNCTIONTYPE as FUNCTION2_509_, function0_.FUNKTION as FUNKTION509_, function0_.MODUL as MODUL509_ from FUNKTION function0_, BENUTZERFUNKTION functionus1_, APPLICATION_FUNCTION functionap2_ where function0_.FUNKTIONID=functionus1_.FUNKTIONID and function0_.FUNKTIONID=functionap2_.FUNKTIONID and functionap2_.ANWID=@P1 and functionus1_.BENUTZERID=@P2', N'@P1 int ,@P2 int ', 1, 1

      It seems that only the first sql, before the union keyword, is executing. The sql after 'union' is ignored.

      If I use the both sqls seperatly they worked.

      Is the UNION keyword supported by ebj-ql?
      Any hint?

      Thanks
      Ralf