Hello,
I have a query which I am trying to sort alphabetically by the String values in a (mapped) collection which is a member of a mapped persistent class. The collection is loaded using a named SQL-query with
load-collection set, and the collection is defined as a
set in the hibernate mapping definition. The class property is defined as
Set<String>.
Now I need to use an ORDER BY statement in HQL to alphabetically order the Invoice records by the values in the collection. The query I'm trying is:
Code:
SELECT i FROM Invoice i WHERE i.deleted=0 ORDER BY i.shipToBranchNames ASC
This produces the error
ORA-00936: missing expression from Oracle. I've also tried:
Code:
SELECT i FROM Invoice i LEFT JOIN i.shipToBranchNames AS branchNames WHERE i.deleted=0 ORDER BY branchNames ASC
Which gives the error
ORA-00942: table or view does not exist.
I've researched and researched this topic, and I can't for the life of me find any documentation regarding sorting alphabetically on items in a collection. No, I don't need to sort the collection itself, it's already doing that fine; I just need a list of invoices sorted by the name(s) of the branch(es) associated with it.
The relevant XML for the schema is:
Code:
<hibernate-mapping>
<class name="org.bah.common.Branch" table="Branches">
<id name="id" type="long" column="branchId" >
<generator class="native"/>
</id>
<property name="name">
<column name="name" not-null="true" length="255"/>
</property>
<property name="displayNameId" column="displayNameId"/>
<property name="deleted">
<column name="isDeleted" index="branches_deletedIndex" default="0"/>
</property>
</class>
<class name="org.bah.acquisitions.Invoice" table="Invoices">
<id name="id" type="long" column="invoiceId" >
<generator class="native"/>
</id>
<set name="shipToBranchNames" cascade="none" inverse="true" fetch="subselect">
<key column="invoiceId" not-null="true"/>
<element column="name" type="java.lang.String" not-null="true" unique="true"/>
<loader query-ref="invoiceShipToBranches"/>
</set>
</class>
<sql-query name="invoiceShipToBranches">
<load-collection alias="b" role="org.bah.acquisitions.Invoice.shipToBranchNames" lock-mode="read"/>
SELECT DISTINCT INVOICEID, b.BRANCHID, m.NAME
FROM OrderItems oi
JOIN Branches b ON oi.SHIPTOBRANCHID=b.BRANCHID
JOIN Messages m ON b.DISPLAYNAMEID=m.MESSAGEID
WHERE oi.INVOICEID=? AND oi.ISDELETED=0
</sql-query>
</hibernate-mapping>
I appreciate any suggestions and/or help with this issue.
Thanks in advance!
Login
FAQ
Search