SELECT clause in FROM clause in Hibernate

bvikram_singh · **Joined:** Sat Jul 30, 2005 7:44 am **Posts:** 15

Hi everybody!

I am using hibernate3.0 to retrieve data from database. But I am facing problem in using select clause in From clause. for example:

select

sum(sTotal) + sum(Disc) as Total

From (
select disc.sDiscount as Disc from discount as disc,
select pos.Total as sTotal from PosTables as pos
)

This query is not original one but it will use select clause in from clause, as it is given above. I have doubt tht this functionlity is not supported in hibernate.Please let me know whether such query is possible in HQL or not . Thanks in advance

scesbron · **Joined:** Thu Sep 04, 2003 8:23 am **Posts:** 368

I don't understand why you do a subselect here. You can do :

Code:

select sum(dis.sDiscount) + sum(pos.Total) as Total from Disc disc, PosTables pos

for more details you can see the chapter on subqueries in the doc http://www.hibernate.org/hib_docs/v3/re ... subqueries

bvikram_singh · **Joined:** Sat Jul 30, 2005 7:44 am **Posts:** 15

Hi Seb Thanks a lot for replying, This query is just small part of whole query, Actually I have to get data from different table on the basis of some condition. As a result I can not put it into single select statement

My query has this format(Not complete).

select
rest,
sum(sTotal) + sum(Disc) as Total
From (
select(
Loc.RestaurntID as rest,
select disc.sDiscount as Disc from discount as disc where disc.sDiscount>10,
select pos.Total as sTotal from PosTables as pos where pos.Total>12
From Loc
)

Let me know if tehre is another way of doing it, thanks

scesbron · **Joined:** Thu Sep 04, 2003 8:23 am **Posts:** 368

For me this is

Code:

select rest, sum(disc.sDiscount) + sum(pos.Total) as Total 
from discount disc, PosTables pos, Loc loc
where dis.sDiscount > 10 and pos.Total > 12

I really don't understand your query because there is no join at all, so maybe I am wrong

bvikram_singh · **Joined:** Sat Jul 30, 2005 7:44 am **Posts:** 15

Hi seb, thanks, I am just trying to convert my oracle query into hibernate which requires many subquery,here is complete query

Code:

[b]SELECT
   Restaurant_Name AS RestaurantName,
   SUM( SumOfTotal)  + SUM(Inner_Discount) -  SUM(giftpaid) AS TotalPrice,
   SUM( SumOfTotal)  + SUM(Inner_Discount) -  SUM(giftpaid) AS TotalPrice1,
   SUM(SumOfSubTotal) - SUM(giftpaid)  AS netsale,
   SUM( SumOfTax) AS  SumTax,
   SUM(SumOfTotal) -  SUM(giftpaid)  AS  adjustedGross,
   Close_Date AS CloseDate,
   SUM(Royalty) AS TotRoyalty,
   SUM(National_Ad) AS NationalAd,
   SUM(Co_Op) AS CoOp,
   Restaurant_ID,
   SUM(Inner_Discount) AS disc,
   SUM(SoldPrice) AS Negative_coupon,
   COUNT(check1) AS checkNO,
   SUM(coupons) AS  Positive_coupon
FROM
   (      
      SELECT 
          LOCATION.RestaurantName AS Restaurant_Name,
          SUM(POSTABLES.SubTotal) AS SumOfSubTotal,
          SUM(POSTABLES.Tax) AS SumOfTax,
          SUM(POSTABLES.subTotal) AS SumOfTotal,
          COUNT(POSTABLES.CheckNUM) AS check1,
          POSTABLES.CloseDate AS Close_Date,
          POSTABLES.RestaurantID AS Restaurant_ID,
          SUM((SELECT SUM(POSTRANS.DISCOUNT) FROM POSTRANS, POSTABLES WHERE POSTRANS.Checknum=POSTABLES.Checknum))Inner_Discount,         
          SUM((SELECT SUM(POSTRANS.Sold) FROM POSTRANS WHERE POSTRANS.Checknum=POSTABLES.Checknum AND POSTRANS.SubGroup = 55 GROUP BY Checknum)) coupons,
          SUM((SELECT SUM(POSTRANS.Sold) FROM POSTRANS WHERE POSTRANS.Checknum=POSTABLES.Checknum AND POSTRANS.SubGroup = 86 GROUP BY Checknum)) SoldPrice,
          SUM((SELECT NVL(SUM(POSTRANS.Sold),0) FROM  POSTRANS WHERE POSTRANS.Checknum=POSTABLES.Checknum AND POSTRANS.ItemName IN ('Gift Certificate') OR POSTRANS.SubGroup = 79 OR POSTRANS.SubGroup = 84)) giftpaid
     FROM
         LOCATION,  POSTABLES 
      WHERE LOCATION.RestaurantID = POSTABLES.RestaurantID 
      GROUP BY LOCATION.RestaurantName, POSTABLES.CloseDate, POSTABLES.RestaurantID      
      HAVING ((POSTABLES.CloseDate BETWEEN TO_DATE('08/03/2005','MM/DD/YYYY') AND TO_DATE('08/04/2005','MM/DD/YYYY')) AND POSTABLES.RestaurantID='1007')
   )  
 GROUP BY Restaurant_Name, Close_Date,Restaurant_ID  [/b]

sorry for inconvenience in reading query :)

scesbron · **Joined:** Thu Sep 04, 2003 8:23 am **Posts:** 368

See the doc I pointed out in my first post to see hibernate subqueries syntax.
It is more like

Code:

select ... from ... where ... in (select ... from ... where ...)

Another advice : try not to convert your query word by word. Hql is not Sql, you can also do simpler query and do some work in the java code

david · **Posted:** Tue Aug 16, 2005 7:34 pm

Virtual tables (from subselects) are not (at the moment) supported by HQL.

snekse · **Joined:** Tue Apr 19, 2005 5:05 pm **Posts:** 3

david wrote:

Virtual tables (from subselects) are not (at the moment) supported by HQL.

Are Subselects supported in the Select clause?

e.g.

select i.issue_id,
(select sum(nvl(c.resolve_date,sysdate)-c.open_date)
from gcmissue_cycle__x c
where c.issue_id = i.issue_id
group by c.issue_id)
from gcmissue__x i

Basically the above query selects all the rows from the parent of a 1-n relationship, and at the same time selects a sum of all the children corresponding to the given parent.

david · **Posted:** Sat Aug 20, 2005 3:47 am

H2 definately no. H3 also no I think. You can check the H3 parser grammar to confirm.