named sql-query dilema

[camelia codarcea]

Hello ranchers,

I am in a rather big dilemma and I really need your help: in the Hibernate Reference book, there are two examples for named sql-query using join between tables:

1. <sql-query name="personsWith">
<return alias="person" class="eg.Person"/>
<return-join alias="address" property="person.mailingAddress"/>
SELECT person.NAME AS {person.name},
person.AGE AS {person.age},
person.SEX AS {person.sex},
adddress.STREET AS {address.street},
adddress.CITY AS {address.city},
adddress.STATE AS {address.state},
adddress.ZIP AS {address.zip}
FROM PERSON person
JOIN ADDRESS adddress
ON person.ID = address.PERSON_ID AND address.TYPE='MAILING'
WHERE person.NAME LIKE :namePattern
</sql-query>

2. <sql-query name="person">
<return alias="pers" class="Person"/>
<return-join alias="emp" property="pers.employments"/>
SELECT NAME AS {pers.*}, {emp.*}
FROM PERSON pers
LEFT OUTER JOIN EMPLOYMENT emp
ON pers.ID = emp.PERSON_ID
WHERE ID=?
</sql-query>

Now, I have tested these two examples on my own DB: tables Season and Game (also 1:n relation, because in a season there are more games played)

The first example is not working when trying to chose only some columns from the table Game (in the example the table ADDRESS).

<sql-query name="myQuery">
<return alias="season" class="SeasonDAO"/>
<return-join alias="game" property="season.games"/>
SELECT season.IDSEASON AS {season.idSeason},
season.YEARS AS {season.years},
game.RESULT AS {game.result},
game.TEAM1 AS {game.team1},
game.TEAM2 AS {game.team2}
FROM Season season JOIN Game game ON season.IDSEASON = game.IDSEASON
<sql-query>
In this case I get "org.hibernate.QueryException: No column name found for property [result] for alias [game] ".

But the second example is working also for me (displaying all column for the second table) :

<sql-query name="myQuery">
<return alias="season" class="SeasonDAO"/>
<return-join alias="game" property="season.games"/>
SELECT season.IDSEASON AS {season.idSeason},
season.YEARS AS {season.years},
{game.*}
FROM Season season JOIN Game game ON season.IDSEASON = game.IDSEASON
</sql-query>


So, my first question is: why do I have to use the column names with capital letters (IDSEASON, YEARS, etc), even though in the database the column name are not like that (IdSeason, Years, etc)?

Second question is: why it doesn't work for me when I choose what column to take from the Game table ? Am I missing something ?

Thank you