Hibernate version: 1.2 b3
Why couldn't I do this?
Code:
SELECT COALESCE(SUM(order.Qty), 0) FROM Order as order WHERE order.OrderCategory = 'ABC'
The error is
Code:
NHibernate.QueryException: alias or expression expected in SELECT
at NHibernate.Hql.Classic.SelectParser.Token(String token, QueryTranslator q) in c:\net\nhibernate\nhibernate\src\NHibernate\Hql\Classic\SelectParser.cs:line 90
at NHibernate.Hql.Classic.ClauseParser.Token(String token, QueryTranslator q) in c:\net\nhibernate\nhibernate\src\NHibernate\Hql\Classic\ClauseParser.cs:line 103
at NHibernate.Hql.Classic.ClauseParser.End(QueryTranslator q) in c:\net\nhibernate\nhibernate\src\NHibernate\Hql\Classic\ClauseParser.cs:line 135
at NHibernate.Hql.Classic.PreprocessingParser.End(QueryTranslator q) in c:\net\nhibernate\nhibernate\src\NHibernate\Hql\Classic\PreprocessingParser.cs:line 173
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q) in c:\net\nhibernate\nhibernate\src\NHibernate\Hql\Classic\ParserHelper.cs:line 34
at NHibernate.Hql.Classic.QueryTranslator.Compile() in c:\net\nhibernate\nhibernate\src\NHibernate\Hql\Classic\QueryTranslator.cs:line 158
at NHibernate.Hql.Classic.QueryTranslator.Compile(IDictionary replacements, Boolean scalar) in c:\net\nhibernate\nhibernate\src\NHibernate\Hql\Classic\QueryTranslator.cs:line 122
at NHibernate.Impl.SessionFactoryImpl.GetQuery(String queryString, Boolean shallow, IDictionary enabledFilters) in c:\net\nhibernate\nhibernate\src\NHibernate\Impl\SessionFactoryImpl.cs:line 512
at NHibernate.Impl.SessionImpl.GetQueries(String query, Boolean scalar) in c:\net\nhibernate\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1769
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results) in c:\net\nhibernate\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1736
at NHibernate.Impl.SessionImpl.Find<T>(String query, QueryParameters parameters) in c:\net\nhibernate\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1719
at NHibernate.Impl.QueryImpl.List<T>() in c:\net\nhibernate\nhibernate\src\NHibernate\Impl\QueryImpl.cs:line 88
I guess the problem is the literal
0. The parser is expecting alias/expression, but not literal. I tried parameterizing the
0 but it does not work either.
The reason I want the COALESCE(xxx, 0) is that the query returns null when no rows are matched but long otherwise. This breaks the generic Find<long?>().
Any help is welcome. Thanks.
Ken Tong