Hi
I need to write an HQL query that selects an attribute based on the first letter of the name.
For example, if I have an object w/ name and weight, I need to sum all the weights for all objects starting w/ A, then B, etc etc.
What is the code for this in HQL? I'm pretty sure I ahve to use REGEXP, but that's all I know.. I'm very new to HQL and don't really know SQL either.
Thanks for the help
Craig

I don't think you can do this directly in an efficient manner but I'm not a SQL guru so take this with a grain of salt. What I would do is have another column that contains the first lettter of the name, then use it to group your result:

select cat.short_name, sum(cat.weight)
from Cat cat
group by cat.short_name

(where short_name is the new column)

Well that's pretty much exactly what I want to do...but I can't actually touch the database to make a new column so that's not going to work.
But ya, if anyone knows how to write this code..

select name.firstLetter, sum(weight)

that would be a HUGE help

thanks
Craig

First, I think you should take the time to read a SQL tutorial or take a course. I think that's worth it. Following you should read the Hibernate documentation (again). I don't think you will understand all of it without any SQL knowledge.

Well, in SQL (e.g. for MySQL) your query would look like this:


In HQL you might test something like this (haven't tried):


Good luck and best regards
Sven

If you also need the first letter of the name, you will need something like this.

SQL:


HQL:

I do need to take a SQL course as well since I didn't know I can do group by on computed values :(

Thanks a lot, I actually just found that substring function in an oracle book, but i was trying to use SUBSTR instead of substring. And ya, I'll take a course when I can, but I need my knowledge NOW haha.

You can in SQL, but I don't know if it's possible with HQL as well. Couldn't find it in the documentation, but I think it is possible.

Read up on formula in the reference doc. I think it might provide what you're looking for without changing the database.