I have written a JBossQL finder using an order by clause. It looks something like:
SELECT DISTINCT OBJECT(o) FROM Table o WHERE o.id1 = ?1 AND o.id2 = ?2 AND o.id3 = ?3 order by o.id4 DESC
The SQL comes out:
SELECT DISTINCT t0_o.gkey FROM ESTIMATE t0_o WHERE t0_o.id1 = ? AND t0_o.id2 = ? AND t0_o.id3 = ? ORDER BY t0_o.id4 DESC
on Oracle, I get the error:
ORA-01791: not a SELECTed expression
Basically saying that since id4 is not in the select portion of the clause, it can't do an order by with it.
This is with JBoss 3.0.7, Oracle 9i and JDK 1.4.1.
Is there some way to write the finder so that this works? Shouldn't JBoss handle this when creating the SQL? Is this a (known) bug?
--Tim