Home » Eclipse Projects » EclipseLink » FetchGroup and column alias
| FetchGroup and column alias [message #651396] |
Sun, 30 January 2011 06:40  |
Eclipse User |
|
|
|
Hi ,
When using fetchgroup in a query the generated sql statement does not add an alias to the columns ( Eclipselink 2.1.2 )
Scenario more like a pseudo code to highlight the concept:
@Entity Country{ id, name , version, ... }
@Entity Region { id, name , version, country:Country , ....}
@Entity City { id, name , version, region: Region, country:Country , .... }
Now i'd like to query the City with the following fetch group
FetchGroup f = new FetchGroup();
f.addAttribute("name");
f.addAttribute("id");
f.addAttribute("region.id");
f.addAttribute("region.name");
f.addAttribute("country.id");
f.addAttribute("country.name");
It fails with ORA-00918: column ambiguously defined
as the sql has :
SELECT ..... t0.ID, t0.NAME, t1.ID, t1.NAME .....
The same bahaviour with nested FetchGroup
FetchGroup country = new FetchGroup();
country.addAttribute("id");
country.addAttribute("code");
f.addAttribute("country", country);
It happens when using left join fetch in jql , works fine without it
Am i missing something ?
[Updated on: Sun, 30 January 2011 09:39] by Moderator |
|
| | |
| Re: FetchGroup and column alias [message #651666 is a reply to message #651540] |
Tue, 01 February 2011 03:14  |
| Eclipse User |
|
|
|
Hi,
Yes i use pagination:
List<E> list = q.setFirstResult(ctx.getResultStart())
.setMaxResults(ctx.getResultSize())
.getResultList();
Here is the eql:
select e from City e left join fetch e.country left join fetch e.region where e.clientId = :clientId and e.country.id = :countryId and e.region.id = :regionId order by e.name
Here is the SQL:
SELECT *
FROM (SELECT /*+ FIRST_ROWS */
a.*, ROWNUM rnum
FROM (SELECT t1.ID AS a1,
t1.ACTIVE AS a2,
t1.CLIENTID AS a3,
t1.CODE AS a4,
t1.CREATEDAT AS a5,
t1.CREATEDBY AS a6,
t1.MODIFIEDAT AS a7,
t1.MODIFIEDBY AS a8,
t1.NAME AS a9,
t1.NOTES AS a10,
t1.VERSION AS a11,
t1.COUNTRY_ID AS a12,
t1.REGION_ID AS a13,
t0.ID,
t0.CODE,
t0.VERSION,
t2.ID,
t2.CODE,
t2.VERSION
FROM BD_COUNTRY t0, BD_REGION t2, BD_CITY t1
WHERE ((((t1.CLIENTID = ?) AND (t1.COUNTRY_ID = ?)) AND
(t1.REGION_ID = ?)) AND
((t0.ID = t1.COUNTRY_ID) AND (t2.ID = t1.REGION_ID)))
ORDER BY t1.NAME ASC) a
WHERE ROWNUM <= ?)
WHERE rnum > ?
As you can see the last six columns are without alias.
If i remove the left join fetch from the query it works as expected :
select e from City e where ....etc ..
[Updated on: Tue, 01 February 2011 03:16] by Moderator |
|
| | |
Goto Forum:
Current Time: Wed Jul 23 12:47:39 EDT 2025
Powered by FUDForum. Page generated in 0.31945 seconds |
Home