Row_number() not working
Description
Environment
Activity
Gyana Ranjan Panda December 15, 2010 at 9:25 PM
I Executed the following query:
select vw.PersonId,vw.CardNumber,vw.CardText,ROW_NUMBER() over (partition by vw.PersonId order by vw.PersonId) as rn from VwExportPerson vw Where vw.AccountId = 22 and (vw.SectionId = 600 or vw.PublishedSecId = 600 ) and (vw.CardStatusLangLvId = 1 or vw.CardStatusLangLvId is null) and (vw.CardLangLvId = 1 or vw.CardLangLvId is null) and (vw.LocLangLvId = 1 or vw.LocLangLvId is null)
and got the exception:
Spring.Data.NHibernate.HibernateQueryException was caught
Message="undefined alias or unknown mapping: ROW_NUMBER [select vw.PersonId,vw.CardNumber,vw.CardText,ROW_NUMBER() over (partition by vw.PersonId order by vw.PersonId) as rn from Risco.Rsp.Ac.AMAC.DomainObjects.VwExportPerson vw Where vw.AccountId = 22 and (vw.SectionId = 600 or vw.PublishedSecId = 600 ) and (vw.CardStatusLangLvId = 1 or vw.CardStatusLangLvId is null) and (vw.CardLangLvId = 1 or vw.CardLangLvId is null) and (vw.LocLangLvId = 1 or vw.LocLangLvId is null)]"
Source="Risco.Rsp.Ac.AMAC.DAO.ExportsMgmt"
QueryString="select vw.PersonId,vw.CardNumber,vw.CardText,ROW_NUMBER() over (partition by vw.PersonId order by vw.PersonId) as rn from Risco.Rsp.Ac.AMAC.DomainObjects.VwExportPerson vw Where vw.AccountId = 22 and (vw.SectionId = 600 or vw.PublishedSecId = 600 ) and (vw.CardStatusLangLvId = 1 or vw.CardStatusLangLvId is null) and (vw.CardLangLvId = 1 or vw.CardLangLvId is null) and (vw.LocLangLvId = 1 or vw.LocLangLvId is null)"
StackTrace:
at Risco.Rsp.Ac.AMAC.DAO.ExportsMgmt.ExportsDAO.FindByQuery(String searchCriteria)
at CompositionAopProxy_3c515189beab4f0dae539ef520299d5d.FindByQuery(String searchCriteria)
at CompositionAopProxy_0e506741a9a2412d82af82e0d55cd662.FindByQuery(String searchCriteria)
at CompositionAopProxy_674a46136ff342268ee5603751b76da0.FindByQuery(String searchCriteria)
at Risco.Rsp.Ac.AMAC.MH.ExportsMgmt.ExportsResultsHandler.Handle(ExportsResults message)
InnerException: NHibernate.QueryException
Message="undefined alias or unknown mapping: ROW_NUMBER [select vw.PersonId,vw.CardNumber,vw.CardText,ROW_NUMBER() over (partition by vw.PersonId order by vw.PersonId) as rn from Risco.Rsp.Ac.AMAC.DomainObjects.VwExportPerson vw Where vw.AccountId = 22 and (vw.SectionId = 600 or vw.PublishedSecId = 600 ) and (vw.CardStatusLangLvId = 1 or vw.CardStatusLangLvId is null) and (vw.CardLangLvId = 1 or vw.CardLangLvId is null) and (vw.LocLangLvId = 1 or vw.LocLangLvId is null)]"
Source="NHibernate"
QueryString="select vw.PersonId,vw.CardNumber,vw.CardText,ROW_NUMBER() over (partition by vw.PersonId order by vw.PersonId) as rn from Risco.Rsp.Ac.AMAC.DomainObjects.VwExportPerson vw Where vw.AccountId = 22 and (vw.SectionId = 600 or vw.PublishedSecId = 600 ) and (vw.CardStatusLangLvId = 1 or vw.CardStatusLangLvId is null) and (vw.CardLangLvId = 1 or vw.CardLangLvId is null) and (vw.LocLangLvId = 1 or vw.LocLangLvId is null)"
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(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 Spring.Data.NHibernate.FindHibernateCallback.DoInHibernate(ISession session)
at Spring.Data.NHibernate.HibernateTemplate.Execute(IHibernateCallback action, Boolean exposeNativeSession)
InnerException:
Julian Maughan December 15, 2010 at 5:08 AM
This is not an HQL statement. If you have a question about how to use NHibernate, please post it at the users group (http://groups.google.com/group/nhusers).
select t.PersonId, max(case when t.rn =1 then t.CardText else null end) as CardText1, max(case when t.rn =1 then t.CardNumber else null end) as CardNumber1, from (select top 10 vw.PersonId,vw.CardNumber,vw.CardText,ROW_NUMBER() over (partition by vw.PersonId order by vw.PersonId) as rn from VwExportPerson vw Where vw.AccountId = 22 and (vw.SectionId = 600 or vw.PublishedSecId = 600 ) and (vw.CardStatusLangLvId = 1 or vw.CardStatusLangLvId is null) and (vw.CardLangLvId = 1 or vw.CardLangLvId is null) and (vw.LocLangLvId = 1 or vw.LocLangLvId is null))t group by t.PersonId
the above hql query is not executed in Nhibernate.