HI ALL,

I am getting the following error when I am trying to execute the distinct and order by query.
I am getting "not a SELECTed expression" error, since I am selecting the column which iam using in distint clause, why any suggestions would appreciated??


Hibernate version:
Hibernate 3.0



Full stack trace of any exception that occurs:

14:44:27,934 WARN [JDBCExceptionReporter] SQL Error: 1791, SQLState: 42000
14:44:27,934 ERROR [JDBCExceptionReporter] ORA-01791: not a SELECTed expression

14:44:27,934 ERROR [LogInterceptor] RuntimeException in method: public abstract
com.exelixis.diesl.to.ConditionsConditionTypeTO[] com.exelixis.diesl.session.rem
ote.ConditionsService.getProtocolConditions(long) throws java.rmi.RemoteExceptio
n:
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificExceptio
n(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter
.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp
er.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1564)
at org.hibernate.loader.Loader.list(Loader.java:1544)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.ja
va:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:830)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.exelixis.diesl.dao.ConditionsProtocolConditionsDAO.getProtocolCon
ditions(ConditionsProtocolConditionsDAO.java:34)
at com.exelixis.diesl.session.ConditionsServiceBean.getProtocolCondition
s(ConditionsServiceBean.java:209)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(S
tatelessSessionContainer.java:214)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invo
ke(CachedConnectionInterceptor.java:185)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(Stat
elessSessionInstanceInterceptor.java:130)
at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(Service
EndpointInterceptor.java:51)
at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidation
Interceptor.java:48)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInte
rceptor.java:105)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxIntercep
torCMT.java:335)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:1


Name and version of the database you are using:
Oracle 9i


The generated SQL (show_sql=true):

14:44:27,924 INFO [STDOUT] Hibernate: select distinct conditions1_.CONDITIONTYP
E_ID as CONDITIO1_, conditions1_.CONDITIONTYPE_NAME as CONDITIO2_162_, condition
s1_.ISACTIVE as ISACTIVE162_, conditions1_.CONDITIONALLENGTH as CONDITIO4_162_,
conditions1_.SOURCESYSTEM as SOURCESY5_162_, conditions1_.SITECODE as SITECODE16
2_, conditions1_.DATATYPE as DATATYPE162_ from CONDITIONS.PROTOCOLCONDITION cond
itions0_, CONDITIONS.CONDITIONTYPE conditions1_ where conditions0_.CONDITIONSTYP
E_ID=conditions1_.CONDITIONTYPE_ID and conditions0_.PROTOCOL_ID=? order by condi
tions0_.CONDITIONSTYPE_ID

I guess it caused by your SQL using. I suggest to think over about what field do you need to be distinct.

I am not sure. just a guess.

Its because the field you're ordering by, 'conditions0_.CONDITIONSTYPE_ID', isn't in the 'select' clause. This is an Oracle limitation, but only applies when using the 'distinct' keyword. Maybe remove the 'distinct' and then remove the duplicates manually, or else somehow get 'conditions0_.CONDITIONSTYPE_ID' into the 'select' clause.