Hi - I am trying to build a fairly complex query to achieve the following;
I have set of reference tables that define a building structure (building, floor, area, item). I have a *register* which has events that are linked to the lowest level in the building structure - i.e. items. The linking table is RegisterItem and the event table is RegisterItemEvent
A single register can contain 1000's of buildings and therefore many 1000's of register items/events. I want to add a row limiter to my query, since it can take a long time to return for lots of data. However this limit needs to apply to the buildings and not the RegisterItemEvents, which is the object the query is returning.
So I have tried to build an *in* clause into the query whose subquery only returns a limited number of buildings - using the *top* statement. See below for the query. This query works fine in native SQL but it seems HQL doesn't support *top* unless used via the SetMaxResults() method in IQuery.
Is there anyway to achieve what I am after here in a single HQL query?
Code:
from RegisterItemEvent rie
where <WHERE CLAUSE>
and rie.RegisterItem.Item.Area.Floor.Building.Id in
(
select distinct top 100 sub.RegisterItem.Item.Area.Floor.Building.Id
from RegisterItemEvent sub
where <WHERE_CLAUSE>
)
Any help will be warmly appreciated!
NHibernate version: 1.2.1.4000
Full stack trace of any exception that occurs:NHibernate.QueryException was unhandled
Message="undefined alias or unknown mapping: top [select rie.LevelOfIdentification, rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Building.Name, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Floor.Name, rie.RegisterItem.Item.Area.Id, rie.RegisterItem.Item.Area.Name, rie.RegisterItem.Item.Id, rie.RegisterItem.Item.Name from ngNINE.Amianto.Client.Domain.RegisterItemEvent rie where rie.RegisterItem.Register.Id = :registerId and rie.RegisterItem.DeleteFl = false and rie.Dttm = (select max(inrrie.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrrie where inrrie.RegisterItem.Id = rie.RegisterItem.Id and inrrie.Dttm <= :dttm) and rie.RegisterItem.Item.Area.Floor.Building.Id in ( select distinct top 100 sub.RegisterItem.Item.Area.Floor.Building.Id from ngNINE.Amianto.Client.Domain.RegisterItemEvent sub where sub.RegisterItem.Register.Id = :registerId and sub.RegisterItem.DeleteFl = false and sub.Dttm = (select max(inrsub.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrsub where inrsub.RegisterItem.Id = sub.RegisterItem.Id and inrsub.Dttm <= :dttm) ) order by rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Id ]"
Source="NHibernate"
QueryString="select rie.LevelOfIdentification, rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Building.Name, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Floor.Name, rie.RegisterItem.Item.Area.Id, rie.RegisterItem.Item.Area.Name, rie.RegisterItem.Item.Id, rie.RegisterItem.Item.Name from ngNINE.Amianto.Client.Domain.RegisterItemEvent rie where rie.RegisterItem.Register.Id = :registerId and rie.RegisterItem.DeleteFl = false and rie.Dttm = (select max(inrrie.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrrie where inrrie.RegisterItem.Id = rie.RegisterItem.Id and inrrie.Dttm <= :dttm) and rie.RegisterItem.Item.Area.Floor.Building.Id in ( select distinct top 100 sub.RegisterItem.Item.Area.Floor.Building.Id from ngNINE.Amianto.Client.Domain.RegisterItemEvent sub where sub.RegisterItem.Register.Id = :registerId and sub.RegisterItem.DeleteFl = false and sub.Dttm = (select max(inrsub.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrsub where inrsub.RegisterItem.Id = sub.RegisterItem.Id and inrsub.Dttm <= :dttm) ) order by rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Id "
StackTrace:
at NHibernate.Hql.Classic.PathExpressionParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.Classic.SelectParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ClauseParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ClauseParser.End(QueryTranslator q)
at NHibernate.Hql.Classic.PreprocessingParser.End(QueryTranslator q)
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.Classic.QueryTranslator.Compile()
at NHibernate.Hql.Classic.QueryTranslator.Compile(QueryTranslator superquery)
at NHibernate.Hql.Classic.WhereParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ClauseParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.PreprocessingParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.Classic.QueryTranslator.Compile()
at NHibernate.Hql.Classic.QueryTranslator.Compile(IDictionary replacements, Boolean scalar)
at NHibernate.Impl.SessionFactoryImpl.GetQuery(String queryString, Boolean shallow, IDictionary enabledFilters)
at NHibernate.Impl.SessionImpl.GetQueries(String query, Boolean scalar)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List()
at ngNINE.Common.Domain.SessionWrapper.ExecuteHQL(IQuery query) in d:\Amianto\Common\src\ngNINE\Common\Domain\SessionWrapper.cs:line 217
at ngNINE.Amianto.Client.Controls.RegisterUI.RegisterSearchUI.ExecuteSearch() in D:\Amianto\Amianto\src\ngNINE\Amianto\Client\Controls\RegisterUI\RegisterSearchUI.cs:line 606
at ngNINE.Amianto.Client.Controls.RegisterUI.RegisterSearchUI.btnSearch_Click(Object sender, EventArgs e) in D:\Amianto\Amianto\src\ngNINE\Amianto\Client\Controls\RegisterUI\RegisterSearchUI.cs:line 1027
at System.Windows.Forms.Control.OnClick(EventArgs e)
at Janus.Windows.EditControls.UIButton.OnClick(EventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at ngNINE.Amianto.Client.Main.Program.Main() in D:\Amianto\Amianto\src\ngNINE\Amianto\Client\Main\Program.cs:line 34
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Name and version of the database you are using:Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86) Feb 26 2008 18:15:01 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Code: