-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: help translating SQL to EJBQL in case of a many2many
PostPosted: Fri Apr 25, 2008 4:43 pm 
Beginner
Beginner

Joined: Thu Nov 16, 2006 11:34 am
Posts: 26
Location: Boston
Hibernate version:
3.3.2
Name and version of the database you are using:
Oracle 10g


Three entities - fof, fund & fundPerformance.
Fof (owning entity) has a Many2Many with Fund
fundPerformance has a uni directional OneToMany to Fund.

Basically, fund Performance is tracked for a fund, every month and that fund can belong to one or many Fofs. I need to get all fund performances for all the funds, for the given fof, for a given month.

The SQL (working) query for that is: -

select fp.month, fp.fund_id, ff.fof_id from fund_performances fp,
fofs_funds ff where fp.fund_id = ff.fund_id and fp.month='jan' and ff.fof_id='1';

The problem for HQL/EJBQL is, there is no entity for the joinTable between fof & funds. In that case, how do traverse among a single fund in "funds"? (Fund has "funds")

I can possibly do this if I created a redundant fofFund entity and translate into a ManyToOne, OneToMany mapping but I don't want to create a dummy object just for a join table.

Any help appreciated.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 28, 2008 2:34 pm 
Beginner
Beginner

Joined: Thu Nov 16, 2006 11:34 am
Posts: 26
Location: Boston
Looking at the examples at Sun's JPA, I wrote the HQL with a sub query like this

Code:
select fp from FundPerformance fp where fp.fund.id  IN (select f from Fund f, IN (f.fofs) AS ff where ff.id = ?1)


It seems to have a problem with the query.

Code:
select f from Fund f, IN (f.fofs) AS ff where ff.id = ?1


whether executed independently or within a sub-query. The error is

java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ffs near line 1, column 143 [select fp from xxx.FundPerformance fp where fp.fund.id IN(select f from xxx.Fund f, IN (f.fofs) AS ff where ff.id = ?1)]

Any suggestions, as it is the same example given in Sun's Tutorial

_________________
www.reverttoconsole.com


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

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.