Hi, I have following pseudo SQL query in my code:



Problem I have is that in place of _H_ I would actually like to write 'G + 100'. But if I do it then in normal SQL (SQL Server 2005) there is an error 'G is invalid column'. Is there any clever way in hibernate that would allow me assigning result G to some temp variable/property and reusing it for calculating _H_? Because my current solution would be to use the same select for _H_ and adding 100. Other way would be to insert all rows and in place of _H_ pass null (column is nullable), and then call update statement that would update _H_ with column C + 100. Both these solutions are ugly. Or maybe my query can be rewritten somehow?

Thanks,
morhen

1. What about just creating a field in your entity class, which does the calculation? Like
getH() {
return g + 100 ;
}
2. What about
SELECT E, F, (SELECT COUNT(*) FROM X WHERE X.ID=Y.ID) AS G, (SELECT COUNT(*) FROM X WHERE X.ID=Y.ID) + 100 AS H

Does it execute 2 queries or SQL Server is clever enough to execute one?

Thanks for the reply

My intention was to generate one query that would insert many rows (thousands) for some parent entity without the need of continuous trips between db and server. This subquery (G) is calculated for each row separately, so adding getter for H would be missing the point. (I have to use HQL, but I'm trying to do as much as possible in database).

Unfortunately SQL Server doesn't cache it but executes it twice. If I don't include H then query executes in 10secs, with H it executes in 20secs... :(

I don't know the exact Hibernate syntax, but in SQL you can replace sub-query this join X and Y which does GROUP BY for all X fields and COUNT(*)

Yes, in SQL it is possible, you can write


This would solve my problem. And in this way I would be able to add 100 to the result. Unfortunately I must do it in HQL, not SQL, and HQL supports subqueries only in SELECT and WHERE, subqueries in FROM are not allowed. :(

No need for subqueries.
select x.a, x.b, count(y), count(y) + 199
from x, y
where x.id = y.id
group by x.a, x.b

OR if x has some relation to y

select x.a, x.b, count(y), count(y) + 199
from x join x.y
where x.id = y.id
group by x.a, x.b

something like that

It is a little bit more complicated. This simple COUNT was for brevity only, sorry about that. My real query looks like:


Subquery calculates sequence number for each row. It generally searches the same table as outer table and checks how many ids are lower than the outer id. In C_DOC_ID I want to put some document id which will actually be some start value (like 100) and add sequence numbers calculated in subquery. I've checked that if I put the same query in _H_ then execution time of the whole query will be doubled.

Any ideas?