I am using ODP.net to run the aggregate AVG against an Oracle 10g database. I run this query directly on the database and it works fine:
select avg(ANSCHAFFUNGSKST) from IHObjekt
it returns: 13.4493973163521
Niether the HQL nor the CreateCriteria interfaces successfully execute the query. I recieve an NHibernate 'could not execute query' message. However, I am relatively certain it is an ODP.Net error based on this posting.
There is a solution for Oracle, simply TRUNC the AVG. However, the TRUNC command is different in Oracle versus SQL Server and I need/want to keep my code from being database specific.
Any ideas about how I can reduce the number of decimal points so it fits within a decimal and most importantly, it works on all databases?
Source = NHibernate
StackTrace
- NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
- NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
- NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
- NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)
- NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)
- NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
- NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results)
- NHibernate.Impl.SessionImpl.List(String query, QueryParameters parameters)
- NHibernate.Impl.QueryImpl.List()
- DBTest_NHibernate.MainWindow.ButtonHQLQuery_Click(Object sender, RoutedEventArgs e) in C:\...
InnerException
[System.OverflowException] = {"Die arithmetische Operation hat einen Überlauf verursacht."} ... The arithmetic operation has caused an overflow.
Source = Oracle.DataAccess
StackTrace
- Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx)
- Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
- Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)
- Oracle.DataAccess.Client.OracleDataReader.get_Item(Int32 i)
- NHibernate.Type.DoubleType.Get(IDataReader rs, Int32 index)
- NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
- NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
- NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.GetResultColumnOrRow(Object[] row, IResultTransformer resultTransformer, IDataReader rs, ISessionImplementor session)
- NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)
- NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
- NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
- NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
Some HQL Testing Results
- select TRUNC(AVG(ANSCHAFFUNGSKST),27) from IHObjekt - WORKS (ONLY IN ORACLE)
- select TRUNC(AVG(ANSCHAFFUNGSKST),28) from IHObjekt - DOES NOT WORK
- select AVG(ANSCHAFFUNGSKST) from IHObjekt - DOES NOT WORK
NHibernate Generated SQL
SELECT
AVG(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_0_0_,
COUNT(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_1_0_,
MAX(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_2_0_,
MIN(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_3_0_,
SUM(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_4_0_
FROM
IHOBJEKT IHOBJEKT0_
Only AVG does not work in the above SQL statement on Oracle using ODP.Net. Using SQL Server or the Oracle client it works.