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开发者_如何学C 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.

---

By dissecting the code I was given and cutting it into smaller pieces I can confirm, that you will run into problems when using doubles with excessive amounts of digits right of the comma.

In contrast to the OP, TRUNC(AVG(XXXX)) did not work in my case. However:

TRUNC(doubledigit, intvalue) and ROUND(doubledigit, intvalue)

brought a solution. I tested this with nhibernate and a simple OracleDataReader using odp.net

---

The issue is due to the value being returned will not cast to a .Net Decimal due to the number of values after the decimal point. It seems that even though the value is rounded by .Net, Oracle internally throw an overflow exception.

Frm what I have read Oracle have confirmed this is by design and won't be changed.

Some people are using Trunc or catsing to a string then a double to get round the issue.