Hibernate version:
3.0
Name and version of the database you are using:
Oracle 8.1.7
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="it.vodafone.wsts.wtee.engine.TestCase" table="TESTCASE" lazy="false">
<id
name="id"
column="ID"
type="long">
<generator class="increment"/>
</id>

<property
name="name"
column="NAME"
type="string"/>

<property
name="code"
column="CODE"
type="string"/>

<property
name="description"
column="DESCRIPTION"
type="string"/>

<property
name="executionDetail"
column="EXECUTIONDETAIL"
type="string"/>

<property
name="expectedResult"
column="EXPECTEDRESULT"
type="string"/>

<many-to-one name="service"
class="it.vodafone.wsts.wtee.engine.Service"
column="SERVICE_ID"
unique="true"/>

<many-to-one name="testCaseClassificationStd"
class="it.vodafone.wsts.wtee.engine.TestCaseClassification"
column="TESTCASECLASSIFICATION_STD_ID"
unique="true"/>

<many-to-one name="testCaseClassificationGl"
class="it.vodafone.wsts.wtee.engine.TestCaseClassification"
column="TESTCASECLASSIFICATION_GL_ID"
unique="true"/>

<list name="executionCards" table="TESTCASE_EXECUTIONCARD" lazy="true">
<key column="TESTCASE_ID"/>
<list-index column="ORDERNUMBER"/>
<many-to-many class="it.vodafone.wsts.wtee.engine.ExecutionCard" column="EXECUTIONCARD_ID"/>
</list>


<many-to-one name="testCasePriority"
class="it.vodafone.wsts.wtee.engine.TestCasePriority"
column="TESTCASEPRIORITY_ID"
unique="true"/>

<many-to-one name="vliveRelease"
class="it.vodafone.wsts.wtee.engine.VLiveRelease"
column="VLIVERELEASE_ID"
unique="true"/>

<many-to-one name="resourceProvisioningType"
class="it.vodafone.wsts.wtee.engine.ResourceProvisioningType"
column="RESOURCEPROVISIONINGTYPE_ID"
unique="true"/>

<property
name="enabled"
column="ENABLED"
type="string"/>

</class>

<sql-query name="findTestCaseOfficialResult">
<![CDATA[
select {tc.*}, {ores.*}
from testcase tc, officialresult ores
where tc.id = ores.testcase_id (+)
and tc.service_id= :serviceId
and ores.device_id (+) = :deviceId
and tc.enabled='T'
order by :myOrder
]]>
<return alias="tc" class="it.vodafone.wsts.wtee.engine.TestCase" />
<return alias="ores" class="it.vodafone.wsts.wtee.engine.OfficialResult" />
</sql-query>

</hibernate-mapping>


I use 'findTestCaseOfficialResult' in this way:



but the 'order by' clause don't work. The order is on id column.

The generated SQL (show_sql=true):
Hibernate: select device0_.ID_DEVICE as ID1_0_, device0_.MODELLO as MODELLO0_0_, device0_.VERSIONE_SW as VERSIONE3_0_0_, device0_.USERAGENT as USERAGENT0_0_, device0_.HEADER_HTTP as HEADER5_0_0_ from SUN.T_DEVICE device0_ where device0_.ID_DEVICE=?
Hibernate: select service0_.ID as ID5_, service0_.DESCRIPTION as DESCRIPT2_13_5_, service0_.NAME as NAME13_5_, service0_.VERSION as VERSION13_5_, service0_.CODE as CODE13_5_, service0_.ENABLED as ENABLED13_5_, testcaseli1_.SERVICE_ID as SERVICE7___, testcaseli1_.ID as ID__, testcaseli1_.ID as ID0_, testcaseli1_.NAME as NAME14_0_, testcaseli1_.CODE as CODE14_0_, testcaseli1_.DESCRIPTION as DESCRIPT4_14_0_, testcaseli1_.EXECUTIONDETAIL as EXECUTIO5_14_0_, testcaseli1_.EXPECTEDRESULT as EXPECTED6_14_0_, testcaseli1_.SERVICE_ID as SERVICE7_14_0_, testcaseli1_.TESTCASECLASSIFICATION_STD_ID as TESTCASE8_14_0_, testcaseli1_.TESTCASECLASSIFICATION_GL_ID as TESTCASE9_14_0_, testcaseli1_.TESTCASEPRIORITY_ID as TESTCAS10_14_0_, testcaseli1_.VLIVERELEASE_ID as VLIVERE11_14_0_, testcaseli1_.RESOURCEPROVISIONINGTYPE_ID as RESOURC12_14_0_, testcaseli1_.ENABLED as ENABLED14_0_, testcasecl2_.ID as ID1_, testcasecl2_.VALUE as VALUE17_1_, testcasecl2_.DESCRIPTION as DESCRIPT3_17_1_, testcasecl2_.SCOPE as SCOPE17_1_, testcasecl3_.ID as ID2_, testcasecl3_.VALUE as VALUE17_2_, testcasecl3_.DESCRIPTION as DESCRIPT3_17_2_, testcasecl3_.SCOPE as SCOPE17_2_, testcasepr4_.ID as ID3_, testcasepr4_.DESCRIPTION as DESCRIPT2_18_3_, testcasepr4_.VALUE as VALUE18_3_, vliverelea5_.ID as ID4_, vliverelea5_.DESCRIPTION as DESCRIPT2_22_4_, vliverelea5_.VALUE as VALUE22_4_ from SERVICE service0_, TESTCASE testcaseli1_, SUN.TESTCASECLASSIFICATION testcasecl2_, SUN.TESTCASECLASSIFICATION testcasecl3_, TESTCASEPRIORITY testcasepr4_, SUN.VLIVERELEASE vliverelea5_ where service0_.ID=testcaseli1_.SERVICE_ID(+) and testcaseli1_.enabled(+)='T' and testcaseli1_.TESTCASECLASSIFICATION_STD_ID=testcasecl2_.ID(+) and testcaseli1_.TESTCASECLASSIFICATION_GL_ID=testcasecl3_.ID(+) and testcaseli1_.TESTCASEPRIORITY_ID=testcasepr4_.ID(+) and testcaseli1_.VLIVERELEASE_ID=vliverelea5_.ID(+) and service0_.ID=?
Hibernate: select tc.ID as ID0_, tc.NAME as NAME14_0_, tc.CODE as CODE14_0_, tc.DESCRIPTION as DESCRIPT4_14_0_, tc.EXECUTIONDETAIL as EXECUTIO5_14_0_, tc.EXPECTEDRESULT as EXPECTED6_14_0_, tc.SERVICE_ID as SERVICE7_14_0_, tc.TESTCASECLASSIFICATION_STD_ID as TESTCASE8_14_0_, tc.TESTCASECLASSIFICATION_GL_ID as TESTCASE9_14_0_, tc.TESTCASEPRIORITY_ID as TESTCAS10_14_0_, tc.VLIVERELEASE_ID as VLIVERE11_14_0_, tc.RESOURCEPROVISIONINGTYPE_ID as RESOURC12_14_0_, tc.ENABLED as ENABLED14_0_, ores.ID as ID1_, ores.REMARKS as REMARKS6_1_, ores.VALUE as VALUE6_1_, ores.EXECUTIONDATE as EXECUTIO4_6_1_, ores.TESTCASE_ID as TESTCASE5_6_1_, ores.DEVICE_ID as DEVICE6_6_1_, ores.USER_ID as USER7_6_1_ from testcase tc, officialresult ores where tc.id = ores.testcase_id (+) and tc.service_id= ? and ores.device_id (+) = ? and tc.enabled='T' order by ?


If I don't set the 'order by' clause with the setString() method and I change the sql-query in this way:

<sql-query name="findTestCaseOfficialResult">
<![CDATA[
select {tc.*}, {ores.*}
from testcase tc, officialresult ores
where tc.id = ores.testcase_id (+)
and tc.service_id= :serviceId
and ores.device_id (+) = :deviceId
and tc.enabled='T'
order by tc.name
]]>
<return alias="tc" class="it.vodafone.wsts.wtee.engine.TestCase" />
<return alias="ores" class="it.vodafone.wsts.wtee.engine.OfficialResult" />
</sql-query>

all works fine.

I suppose you cannot make the column you order by a parameter as 'order by' wants a column definition as a parameter and your specifying a string.

This seems logical to me. Can anyone correct me?