Hibernate version: 3.1.3
Name and version of the database you are using: Oracle 10.2.0.2
In an HQL query using a constructor (select new MyObject), is it possible to order by a column that is mentioned in the constructor only as a subselect?
I have a class Customer with two attributes: String name and List<CommunicationMedium> comMedia.
But I only want to select a CustomerDTO with the attributes String name and String primaryEmail.
And I want the list ordered by the customers' primary e-mail addresses.
I have a class CommunicationMedium that covers e-mail addresses, phone numbers, fax numbers etc., using a "value" (the actual address/number) and a "type" ("email, "phone", "fax").
The query is supposed to match the following constructor:
Code:
public CustomerDTO(String name, String primaryEmail) { ... }
Here's my query:
Code:
select new CustomerDTO
(
name,
(
select outer_medium.value
from Customer outer_customer
join outer_customer.comMedia outer_medium
where index(outer_medium) in
(
select minindex(inner_medium)
from Customer inner_customer
join inner_customer.comMedia inner_medium
where
inner_medium.type = 'email'
and inner_medium in elements(inner_customer.comMedia)
and inner_customer.id = customer.id
)
and outer_customer.id = customer.id
) as primaryEmail
)
order by primaryEmail
But that doesn't work. My database doesn't know the "primaryEmail" in the order-by clause:
Code:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at ...
Caused by: java.sql.SQLException: ORA-00904: "PRIMARYEMAIL": invalid identifier
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 69 more
Login
FAQ
Search
