-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
canton
 Post subject: Literal in HQL SELECT
PostPosted: Fri Feb 09, 2007 12:41 am 
Regular
Regular

Joined: Tue Aug 08, 2006 4:28 am
Posts: 96
Location: Hong Kong
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
Top
 Profile  
 
MurhyJ
 Post subject: Literal in HQL Select
PostPosted: Mon Feb 12, 2007 4:55 pm 
Newbie

Joined: Sat Jan 20, 2007 3:16 pm
Posts: 9
Hi,

I was searching for something and I ran into this.

I am quite new to Hibernate. From the error, it seems to me that you may need to provide an alias to the COALESCE expression, you could try:

SELECT COALESCE(SUM(order.Qty), 0) AS TotalQty FROM Order as order WHERE order.OrderCategory = 'ABC'.

Murthy
Top
 Profile  
 
canton
 Post subject:
PostPosted: Mon Feb 12, 2007 9:55 pm 
Regular
Regular

Joined: Tue Aug 08, 2006 4:28 am
Posts: 96
Location: Hong Kong
Murthy,

It does not work. Same error as before. Thanks anyway.

Ken Tong
Top
 Profile  
 
MurhyJ
 Post subject: http://forum.hibernate.org/viewtopic.php?p=2340805#2340805
PostPosted: Mon Feb 12, 2007 10:52 pm 
Newbie

Joined: Sat Jan 20, 2007 3:16 pm
Posts: 9
Ken,

It looks to me the sql functions such as coalesce/isNull don't work in the select list. The same expression coalesce(something, 0) would work if used in the where clause (I tried this).

If you really need it, it would probably work if you used ISQLQuery interface and stick to ANSI SQL (COALESCE is ANSI, IsNull is not) and if your requirements permit you.

I am a novice and following this since I will likely need these types of things as I work through NHibernate. Let me know if you find out more on this.

regards
Murthy
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.