Hibernate version: 1.0.2

Name and version of the database you are using: Oracle 10g

The generated SQL (show_sql=true):
2006-05-17 19:04:35,900 [1544] DEBUG NHibernate.SQL - SELECT DISTINCT ESAARKEY0_ FROM ESAMI Exams WHERE SPKEY0_ = 'XXX'
2006-05-17 19:04:36,025 [1544] DEBUG NHibernate.Util.ADOExceptionReporter - SQL Exception
Oracle.DataAccess.Client.OracleException ORA-00904: "ESAARKEY0_":

my field name is ESAARKEY without 0_ suffix!

C# code
string szQuery = "SELECT DISTINCT {Exams.ID} FROM ESAMI Exams WHERE {Exams.MainCategoryID} = 'XXX' ";
IQuery query = this.m_session.CreateSQLQuery(szQuery,"Exams",typeof(Exams));

any idea ?

i'm thinking this is a bug; i did a lot of retries but i always get the same bad SQL query (with 0_ suffix).

no comment ?

The things in curly braces are aliases, not column names, so the query should look like this:

string szQuery = "SELECT DISTINCT ESAARKEY {Exams.ID} FROM ESAMI {Exams} WHERE {Exams.MainCategoryID} = 'XXX' ";

See http://www.hibernate.org/hib_docs/nhibernate/html/querysql.html for examples and documentation.

i've already tried that solution but i get this error in the log

2006-05-22 10:18:19,225 [312] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands :1
2006-05-22 10:18:19,225 [312] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: SELECT DISTINCT ESAARKEY ESAARKEY0_ FROM ESAMI Exams WHERE SPKEY SPKEY0_ = 'CHIRUR'
2006-05-22 10:18:19,240 [312] INFO NHibernate.Loader.Loader - SELECT DISTINCT ESAARKEY ESAARKEY0_ FROM ESAMI Exams WHERE SPKEY SPKEY0_ = 'CHIRUR'
2006-05-22 10:18:19,240 [312] DEBUG NHibernate.SQL - SELECT DISTINCT ESAARKEY ESAARKEY0_ FROM ESAMI Exams WHERE SPKEY SPKEY0_ = 'CHIRUR'
2006-05-22 10:18:19,412 [312] DEBUG NHibernate.Util.ADOExceptionReporter - SQL Exception
Oracle.DataAccess.Client.OracleException ORA-00920: operatore relazionale non valido in Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
in Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
in Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
in Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()
in NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
in NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-05-22 10:18:19,412 [312] WARN NHibernate.Util.ADOExceptionReporter - Oracle.DataAccess.Client.OracleException ORA-00920: operatore relazionale non valido in Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
in Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
in Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
in Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()
in NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
in NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-05-22 10:18:19,412 [312] ERROR NHibernate.Util.ADOExceptionReporter - ORA-00920: operatore relazionale non valido
2006-05-22 10:18:19,412 [312] DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands :0
2006-05-22 10:18:19,428 [312] DEBUG NHibernate.Util.ADOExceptionReporter - error in FindBySQL
Oracle.DataAccess.Client.OracleException ORA-00920: operatore relazionale non valido in Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
in Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
in Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
in Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()
in NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
in NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
in NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
in NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
in NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
in NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
in NHibernate.Loader.SqlLoader.List(ISessionImplementor session, QueryParameters queryParameters)
in NHibernate.Impl.SessionImpl.FindBySQL(String sqlQuery, String[] aliases, Type[] classes, QueryParameters queryParameters, ICollection querySpaces)
2006-05-22 10:18:19,428 [312] WARN NHibernate.Util.ADOExceptionReporter - Oracle.DataAccess.Client.OracleException ORA-00920: operatore relazionale non valido in Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
in Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
in Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
in Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader()
in NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
in NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
in NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
in NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
in NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
in NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
in NHibernate.Loader.SqlLoader.List(ISessionImplementor session, QueryParameters queryParameters)
in NHibernate.Impl.SessionImpl.FindBySQL(String sqlQuery, String[] aliases, Type[] classes, QueryParameters queryParameters, ICollection querySpaces)
2006-05-22 10:18:19,428 [312] ERROR NHibernate.Util.ADOExceptionReporter - ORA-00920: operatore relazionale non valido

and the query in my program is

SELECT DISTINCT ESAARKEY {Exams.ID} FROM ESAMI {Exams} WHERE SPKEY {Exams.MainCategoryID} = 'CHIRUR'

Try without the alias in the WHERE clause:

SELECT DISTINCT ESAARKEY {Exams.ID} FROM ESAMI {Exams} WHERE SPKEY = 'XXX'

i get an exception when nhibernate try to associate resultset to class

{"error in FindBySQL"}
{"Impossibile trovare la colonna specificata nel set di risultati"}
in english
{"Cannot find specified column within the resultset"}

Try again the query in the form I posted first:
string szQuery = "SELECT DISTINCT ESAARKEY {Exams.ID} FROM ESAMI {Exams} WHERE {Exams.MainCategoryID} = 'XXX' ";

note that it's not "WHERE SPKEY {Exams.MainCategoryID} = 'XXX'" - no SPKEY in my version.

{"ORA-00904: \"SPKEY0_\": identificativo non valido"}
in english
{"ORA-00904: \"SPKEY0_\": field not valid"}

why it add that suffix (0_) ? if i create an SQL query NH should use
the fields names or not ?

Hmm, looks like the documentation of SQL queries is wrong in a few places too... Anyway, how about this:

SELECT DISTINCT ESAARKEY {Exams.ID} FROM ESAMI WHERE ESAMI.SPKEY = 'XXX'

By the way, you can only load complete objects with NHibernate's SQL queries, so you have to specify all of the properties of Exams object (if there are any besides the ID). This may be the cause of some errors you got earlier.

perfect, now it's working fine; i add all the key properties.

i think too that the documentation is really wrong

thanks a lot sergey =) (rated)