Re: Incorrect SQL generated

[Jakob Braeuchi]

hi wallace,

is this a sql-server issue only, or can you reproduce it on hsqldb as 
well ?

jakob

Gelhar, Wallace Joseph wrote:

I have a model that is as follows:

    Person              Driver (Interface)
         |                 |              |
PersonImpl      DriverManaged   DriverUnmanaged
         |                 |              |
        DriverManagedImpl               DriverUnmanagedImpl
                
DriverManagedImpl extends PersonImpl implements DriverManaged, Driver,
Person
Both DriverManagedImpl and DriverUnmanagedImpl have a M:N collection to
another class Reservation.
The mapping for DriverManagedImpl uses the "super" reference descriptor
to mapped on multiple joined tables as follows:
<class-descriptor 
  class="uwec.fpm.persistence.fleet.DriverManagedImpl"
  table="dbo.tblFltDriverManaged" >
  
  <field-descriptor
    name="id"
    column="UWECId"
    jdbc-type="INTEGER"
    primarykey="true"
    readonly="true"
  />
...
  <reference-descriptor
    name="super"
    class-ref="uwec.fpm.persistence.fleet.PersonImpl"
    refresh="true"
    auto-retrieve="true"
    auto-update="true" >
    <foreignkey field-ref="id" />
  </reference-descriptor>
  <collection-descriptor
        name="reservations"
        element-class-ref="uwec.fpm.persistence.fleet.ReservationImpl"
        auto-retrieve="true"
        auto-update="true"
        auto-delete="false"
        proxy="true" 
        indirection-table="tblFltReservationDriver" >
        <fk-pointing-to-this-class column="DriverID" />
        <fk-pointing-to-element-class column="ReservationID" />
   </collection-descriptor>
</class-descriptor>

Using SQL Server 2000, I get the following SQL when querying for
Driver.class which is not valid.  I think the cross join should be an
INNER JOIN between tblFltDriverManaged and tblFltReservationDriver.  SQL
Server does not recognize the A0 alias on the cross join.
SELECT A0.UWECId,
	A0.IsVanAuthorized,
	A0.Updated,
	A0.IsCarAuthorized 
FROM dbo.tblFltDriverManaged A0, tblFltReservationDriver INNER JOIN
FACMGT.dbo.tblMMPerson A1 
ON A0.UWECId=A1.UWECId 
WHERE (tblFltReservationDriver.ReservationID =  '510' ) AND
tblFltReservationDriver.DriverID = A0.UWECId

Any suggestions on where to start investingating where to fix this?

~~~~~~~~~~~~~~~~~~~~~~~ 
Wallace J Gelhar 
University of Wisconsin - Eau Claire 
Facilities Planning & Management 
Computer Information Systems 
715-836-3411 

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]