Hello, I am getting an error when i use the following query.

Query query = session.createQuery("from com.xerox.hc.model.common.device.DeviceInfo as d where d.serialNumber in (:serialNoList) order by d.DeviceProxyLink.proxyId asc");

Here DeviceProxyLink is the relationship that i have created in the DeviceInfo map. Here is the section of the map.

<set name="DeviceProxyLink" table="PROXY_DEVICE_LIST">
<key column="SERIAL_NUMBER"/>
<one-to-many class="com.xerox.hc.model.common.device.ProxyDeviceList"/>
</set>

DeviceInfo is the 1st table (Serial_Number is the PK)
ProxyDeviceLink is the 2nd table (Serial_number and Proxy_Id are PK's)
I have only proxy_id and serial_number in the 2nd table.

The same problem happens if i want to do a query (or) order by (or)group by using the elements in the 2nd table (Basically proxy_id)

Any help would be appreciated. Here is the stack trace.

net.sf.hibernate.QueryException: expecting 'elements' or 'indices' after: proxyId [from com.xerox.hc.model.common.device.DeviceInfo as d where d.serialNumber in (:serialNoList0_, :serialNoList1_, :serialNoList2_, :serialNoList3_, :serialNoList4_, :serialNoList5_, :serialNoList6_, :serialNoList7_, :serialNoList8_, :serialNoList9_, :serialNoList10_) order by d.DeviceProxyLink.proxyId asc]
at net.sf.hibernate.collection.CollectionPropertyMapping.toType(CollectionPropertyMapping.java:53)
at net.sf.hibernate.hql.PathExpressionParser.getPropertyType(PathExpressionParser.java:244)
at net.sf.hibernate.hql.PathExpressionParser.end(PathExpressionParser.java:283)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.OrderByParser.token(OrderByParser.java:30)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:152)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:141)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:287)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1488)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1459)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:45)
at com.xerox.hc.reg2.configuration.session.ConfigurationManagerSessionBean.getDeviceInfo(ConfigurationManagerSessionBean.java:314)

Thanks
Elango

Thanks Gavin, i already got that working by explicitly saying join in the query. But my question is

is there any way we can acheive this without doing explicit join in the query? In other words

What is the use of creating link between 2 tables using the <set> attribute in the hibernate map? It fetches the record in the 2nd table when you have that link mapped. They why can't we say something like

order by d.DeviceProxyLink.proxyId ( as it already got the records from the link table where the column proxy_id is)?

If i can't do like this, it means that the <Set> attribute orwhatever else we can use to link 2 tables are just to do normal select query and we can't do any queries based on some criteria (or) group by (or) order by on the 2nd table column. Is that true?

Am i missing something?

Thanks
Elango