Hi,

I am using JBoss 3.0 final on Win2000 with Oracle 8.1.7 and experience the following problem/bug/whatever:

I have the following JBoss-QL statement in the jbosscmp-jdbc.xml:

<jboss-ql><![CDATA[
SELECT OBJECT(co)
FROM Companies co, IN(co.profiles) fp
WHERE fp.benuId = ?1
ORDER BY co.befiName
]]></jboss-ql>

Jboss generates the following two SQL statements out of this:

14:57:28,627 DEBUG [findByFirmenprofile] Executing SQL: SELECT t0_bf.BEFI_ID FROM BESTELLFIRMEN t0_bf, BEST_NUTZER t1_fp, FIRMENPROFILE t2_bf_firmenprofile_bf_R
ELATIO WHERE (t1_fp.BENU_ID = ?) AND (t0_bf.BEFI_ID=t2_bf_firmenprofile_bf_RELATIO.FIPR_BEFI_ID AND t1_fp.BENU_ID=t2_bf_firmenprofile_bf_RELATIO.FIPR_BENU_ID) ORDER BY t0_bf.BEFI_ANZEIGENAME ASC

14:57:28,657 DEBUG [Bestellfirmen] Executing SQL: SELECT BEFI_ID,BEFI_ADRE_ORT,
BEFI_ADRE_LAENDERKENNZEICHEN, BEFI_ANZEIGENAME, BEFI_FIRMENNAME, BEFI_LOGINNAME,
BEFI_ADRE_PLZ FROM BESTELLFIRMEN WHERE (BEFI_ID=?) OR (BEFI_ID=?) OR (BEFI_ID=?
) OR (BEFI_ID=?) OR (BEFI_ID=?)

The problem is, the first statement returns the primary keys in the right order, but when JBoss fetches the actual data, the order get lost, because Oracle returns the records in its own order, when you don't attach an ORDER BY clause.

Is there any way to get around this?

Thanks a lot,

Thorsten.