Row_number() not working

Description

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.

Environment

None

Activity

Show:

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).

Not an Issue

Details

Assignee

Reporter

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created December 15, 2010 at 1:23 AM
Updated December 15, 2010 at 9:25 PM
Resolved December 15, 2010 at 5:08 AM
Who's Looking?