Hi,
I am using JBoss 4.0.3SP1 with the included hibernate3.jar. We deploy a HAR within an EAR. The whole mapping is ok and all beans/tables can be used.
Our only problem is a HQL select which is translated into a malformed Oracle SQL.
Code:
select s, tgs.gradeSubject.grade,
s.scheduleRoster,
tgs.teacherFacility.facility,
tgs.gradeSubject.subject,
tgs.gradeSubject
from Schedule s inner join s.teacherGradeSubjects tgs
where tgs.teacherFacility.teacherFacilityId = ?
and s.validFrom >= ?
and s.validUntil <= ?
This maps to:
Code:
select *
from esa_schedule schedule0_
inner join esa_scheduleTeacherGradeSubj teachergra1_ on schedule0_.schedule_id=teachergra1_.schedule_id
inner join esa_teacherGradeSubject teachergra2_ on teachergra1_.teacher_grade_subject_id=teachergra2_.teacher_grade_subject_id,
esa_gradeSubject gradesubje3_
inner join esa_grade grade4_ on gradesubje3_.grade_id=grade4_.grade_id
inner join esa_subject subject9_ on gradesubje3_.subject_id=subject9_.subject_id,
esa_teacherFacility teacherfac6_
inner join esa_facility facility7_ on teacherfac6_.facility_id=facility7_.facility_id
inner join esa_scheduleRoster schedulero5_ on schedule0_.schedule_roster_id=schedulero5_.schedule_roster_id
where teachergra2_.teacher_facility_id=teacherfac6_.teacher_facility_id
and teachergra2_.grade_subject_id=gradesubje3_.grade_subject_id
and teachergra2_.teacher_facility_id=?
and schedule0_.valid_from>=?
and schedule0_.valid_until<=?
The problem is in the position of the inner join of the ScheduleRoster.
inner join esa_scheduleRoster schedulero5_ on schedule0_.schedule_roster_id=schedulero5_.schedule_roster_id
It results into an:
ORA-00904: "SCHEDULE0_"."SCHEDULE_ROSTER_ID": invalid identifier
After the "," in the from an reference to the table alias is not possible anymore. If I move the join directly after the
from esa_schedule schedule0_, the select works perfectly. Other possibility is to remove the "," and use inner joins on all conditions.
How can i force such a behaviour or get around that?