Hi All,

I have a question about a left fetch join sql. My HQL is following:

"select e from Event e, Team t left join fetch e.markets " +
"where e.eventStatus='Open' and e.ultimateGroup.groupId = " + " t.ultimateGroup.groupId and t.teamId = ? and t.teamStatus = ?"

Here I would like to fetch list of Event with a Set of Market initialized. The join condition is Team's UltimateGroupID = Event's UltimateGroupID for given TeamId and TeamStatus. I have read the Action book and I could not find an example for deal of this situation. If given condition is from Event itself, that would be easy.

Any help is appreciated.

Mark

Sorry, I did not include the error. The error is:

org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [null] in task 'HibernateAccessor';
nested exception is java.sql.SQLException: ORA-00904: "EVENT0_"."EVENT_ID": invalid identifier

java.sql.SQLException: ORA-00904: "EVENT0_"."EVENT_ID": invalid identifier

The Hibernate sql is:

Hibernate: select event0_.EVENT_ID as EVENT_ID0_, markets2_.MARKET_ID as MARKET_ID1_,
event0_.EVENT_NAME as EVENT_NAME0_, event0_.EVENT_SHORT_NAME as EVENT_SH3_0_,
event0_.EVENT_STATUS as EVENT_ST4_0_, event0_.NUMBER_OF_GAMES as NUMBER_O5_0_,
event0_.CREATION_TIME as CREATION6_0_, event0_.LAST_UPDATE_TIME as LAST_UPD7_0_,
event0_.LAST_UPDATE_BY as LAST_UPD8_0_, event0_.LAST_UPDATE_REASON as LAST_UPD9_0_,
event0_.RECORD_STATUS as RECORD_10_0_, event0_.ULTIMATE_GROUP_ID as ULTIMAT11_0_,
event0_.GROUP_ID as GROUP_ID0_, markets2_.GAME_NUMBER as GAME_NUM2_1_,
markets2_.MARKET_NAME as MARKET_N3_1_, markets2_.MARKET_SHORT_NAME as MARKET_S4_1_,
markets2_.MARKET_DATE as MARKET_D5_1_, markets2_.MARKET_STATUS as MARKET_S6_1_,
markets2_.FACE_VALUE_PRICE as FACE_VAL7_1_, markets2_.MAX_QUANTITY as MAX_QUAN8_1_,
markets2_.IS_FEATURED as IS_FEATU9_1_, markets2_.CREATION_TIME as CREATIO10_1_,
markets2_.LAST_UPDATE_TIME as LAST_UP11_1_, markets2_.LAST_UPDATE_BY as LAST_UP12_1_,
markets2_.LAST_UPDATE_REASON as LAST_UP13_1_, markets2_.RECORD_STATUS as RECORD_14_1_,
markets2_.EVENT_ID as EVENT_ID1_, markets2_.VENUE_ID as VENUE_ID1_,
markets2_.GROUP_ID as GROUP_ID1_, markets2_.ULTIMATE_GROUP_ID as ULTIMAT18_1_,
markets2_.PROGRAM_ID as PROGRAM_ID1_, markets2_.HOME_TEAM_ID as HOME_TE20_1_,
markets2_.AWAY_TEAM_ID as AWAY_TE21_1_, markets2_.MARKET_ID as MARKET_ID__,
markets2_.EVENT_ID as EVENT_ID__ from EVENT_TABLE event0_, TEAM_TABLE team1_
left outer join MARKET_TABLE markets2_ on event0_.EVENT_ID=markets2_.EVENT_ID where
(event0_.EVENT_STATUS='Open' )and(event0_.ULTIMATE_GROUP_ID=team1_.ULTIMATE_GROUP_ID )and
(team1_.TEAM_ID=? )and((team1_.TEAM_STATUS=? ))

You would need to map the association between Team -> UltimateGroup -> Event (hibernate does not allow arbitrary or ad-hoc joins).

Then, the query would look something like: