does order by work at all?

cnsxxx09 Jan 24, 2003 3:14 AM

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?

cnsxxx09 Jan 24, 2003 3:47 AM (in response to cnsxxx09)

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
Actions