1 Reply Latest reply on Jan 24, 2003 3:47 AM by Chris

    does order by work at all?

    Chris Newbie

      Hi,

      I have the following JBoss QL for 3 different queries (see below). They all fail.
      But if I remove the ORDER By clause then they all work.

      Does ORDER BY not work in JBoss3.0.4/JBossWeb? Is this a known bug?

      Thanks (hopefully)

      Chris
      -=-=-=

      These 3 work
      ------------
      SELECT DISTINCT OBJECT(o) FROM Document o where (o.function.functionId BETWEEN ?1 AND ?2)
      SELECT DISTINCT OBJECT(o) FROM Document o where (o.function.functionId >= ?1 AND o.function.functionId <= ?2)
      SELECT DISTINCT OBJECT(o) FROM Document o where (o.function.functionId = ?1)

      These 3 fail
      ------------
      SELECT DISTINCT OBJECT(o) FROM Document o where (o.function.functionId BETWEEN ?1 AND ?2) ORDER BY o.documentCode
      SELECT DISTINCT OBJECT(o) FROM Document o where (o.function.functionId >= ?1 AND o.function.functionId <= ?2) ORDER BY o.documentCode
      SELECT DISTINCT OBJECT(o) FROM Document o where (o.function.functionId = ?1) ORDER BY o.documentCode

      PS - the columns do exist, I get the error "java.lang.ClassCastException: javax.ejb.FinderException"

        • 1. Re: does order by work at all?
          Chris Newbie

          I found the problem from the stack.

          Basically, the SQL statement here does a 'DISTINCT' clause on the id of the table, and I think the ORDER BY in this case must match what is being selected (?) - ie the id. So if I order by the id it will work.

          But I do not want to order by the Id (and neither too do I want duplicates)

          Now what? !

          Chris
          -=-=-=

          09:45:33,873 DEBUG [findByFunction3] Executing SQL: SELECT DISTINCT t0_o.DOCUMENT_ID FROM DOCUMENT t0_o, FUNCTION t1_o_function WHERE ((t1_o_function.FUNCTION_ID = ?)) AND (t0_o.FUNCTION_FK=t1_o_function.FUNCTION_ID) ORDER BY t0_o.DOCUMENT_CODE ASC
          09:45:33,937 DEBUG [findByFunction3] Find failed
          java.sql.SQLException: ORA-01791: not a SELECTed expression