Hello guys,
i have the following hql query:
Code:
select avg(answ.decvalue), answ.Questionid.id from Answers answ where answ.Formid.id = ? and answ.Questionid.id = ? and answ.Noanswer = 0 group by answ.Questionid.id
1st issue:
Executing this query via query.Enumerable() throws an exception. The generated sql as shown below is wrong, the table name within the aggregate function is the one from the hql query, not the alias used in the generated sql.
This problem can be solved by removing the alias within the aggregate function (changing it to " avg(decvalue) ").
2nd issue:
when i change the hql query, it executes - but i only get the first of the two selected columns. Executing the generated sql via SQL Management Studio returns 2 columns, as expected.
3rd issue:
the returned value is int, the table column is decimal. Again, executing the sql via SQL Management Studio returns a decimal column for the aggregate function (and the second column, int).
I'm using NHibernate for just a few months now and are still new to hql, so i assume i have a problem somewhere - just don't know where. Any help would be greatly appreciated.
NHibernate version: 1.0.3.0
Mapping documents:Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="GimtactBase.NHData.Answers,GimtactBase" table="answers" select-before-update="true">
<id name="Id" column="id" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="Formid" column="formid" class="GimtactBase.NHData.Forms,GimtactBase" />
<many-to-one name="Contactid" column="contactid" class="GimtactBase.NHData.Contacts,GimtactBase" />
<many-to-one name="Questionid" column="questionid" class="GimtactBase.NHData.Questions,GimtactBase" />
<property column="decvalue" type="Decimal" name="Decvalue" not-null="true" />
<property column="intvalue" type="Int32" name="Intvalue" not-null="true" />
<property column="boolvalue" type="Boolean" name="Boolvalue" not-null="true" />
<property column="created" type="DateTime" name="Created" not-null="true" />
<property column="text" type="String" name="Text" not-null="true" length="50" />
<property column="noanswer" type="Boolean" name="Noanswer" not-null="true" />
</class>
</hibernate-mapping>
Code:
2007-02-07 09:28:50,302 [2804] ERROR GimtactWeb.Errors - Source: ASP.reporting_frmreport_formsbasereport1_aspx
System.Exception: Anwendungsfehler während Page_Load ---> NHibernate.ADOException: Could not execute query ---> System.Data.SqlClient.SqlException: Das Spaltenpräfix 'answ' stimmt mit keinem in der Abfrage verwendeten Tabellen- oder Aliasnamen überein.
bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
bei System.Data.SqlClient.SqlDataReader.get_MetaData()
bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
bei NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
bei NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
bei NHibernate.Hql.QueryTranslator.GetEnumerable(QueryParameters parameters, ISessionImplementor session)
bei NHibernate.Impl.SessionImpl.Enumerable(String query, QueryParameters parameters)
--- Ende der internen Ausnahmestapelüberwachung ---
bei NHibernate.Impl.SessionImpl.Enumerable(String query, QueryParameters parameters)
bei NHibernate.Impl.QueryImpl.Enumerable()
bei GimtactWeb.Reporting.frmReport_FormsBaseReport1.processAnswers(Forms form) in C:\Dokumente und Einstellungen\d.trilsbeek\Eigene Dateien\Visual Studio 2005\Projects\GimtactWebSvn\GimtactWebSvn\Reporting\frmReport_FormsBaseReport1.aspx.cs:Zeile 113.
bei GimtactWeb.Reporting.frmReport_FormsBaseReport1.onFirstCall(Object sender, EventArgs e) in C:\Dokumente und Einstellungen\d.trilsbeek\Eigene Dateien\Visual Studio 2005\Projects\GimtactWebSvn\GimtactWebSvn\Reporting\frmReport_FormsBaseReport1.aspx.cs:Zeile 31.
bei GimtactWeb.Classes.WebFormBase.Page_Load(Object sender, EventArgs e) in C:\Dokumente und Einstellungen\d.trilsbeek\Eigene Dateien\Visual Studio 2005\Projects\GimtactWebSvn\GimtactWebSvn\Classes\WebFormBase.cs:Zeile 131.
--- Ende der internen Ausnahmestapelüberwachung ---
bei GimtactWeb.Classes.WebFormBase.Page_Load(Object sender, EventArgs e) in C:\Dokumente und Einstellungen\d.trilsbeek\Eigene Dateien\Visual Studio 2005\Projects\GimtactWebSvn\GimtactWebSvn\Classes\WebFormBase.cs:Zeile 141.
bei System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
bei System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
bei System.Web.UI.Control.OnLoad(EventArgs e)
bei System.Web.UI.Control.LoadRecursive()
bei System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Name and version of the database you are using:Microsoft MSDE (SQL Server 8.0)
The generated SQL (show_sql=true):Code:
2007-02-07 09:28:42,959 [2804] DEBUG NHibernate.SQL - select avg(answ.decvalue) as x0_0_, answers0_.questionid as x1_0_ from Contact_Test.dbo.answers answers0_ where (answers0_.formid=@p0) and (answers0_.questionid=@p1) and (answers0_.noanswer=0) group by answers0_.questionid
2007-02-07 09:28:42,959 [2804] DEBUG NHibernate.SQL - @p0 = '14'
2007-02-07 09:28:42,959 [2804] DEBUG NHibernate.SQL - @p1 = '45'
Login
FAQ
Search