2 Replies Latest reply on Apr 7, 2017 2:46 PM by Steven Hawkins

    problem on oData query with FK on the same table

    Mirco Marchitiello Newbie

      Hi,

      we're facing some problems with Teiid 9.1.4 on a particular oData query

      We have a virtual table like this:

       

      EmployeesEntity:

       

      EmployeeID (int)

      FirstName (varchar(50))

      LastName (varchar(50))

      Delegate (int)

      DeputyDelegate (int)

      TimeSheetDelegate (int)

       

      with the following keys

       

      Primary key

      PK_EmployeeID -> EmployeesEntity.EmployeeID

       

      Foreign key

      delegates -> EmployeesEntity.Delegate ref_to: PK_EmployeeID

       

      Foreign key

      deputyDelegates -> EmployeesEntity.DeputyDelegate ref_to: PK_EmployeeID

       

      Foreign key

      timeSheetDelegates -> EmployeesEntity.TimeSheetDelegate ref_to: PK_EmployeeID

       

      that is, a delegate, a deputy delegate or a time sheet delegate is an employee

       

      When we execute an oData query the generated SQL query in Teiid has always the same JOIN ON clause

       

      ex:

       

      1- http://casteiid01.casalelug.ch:8080/odata4/NORTHWIND.1/ViewNorthwind/EmployeesEntity(8)/delegates

      2- http://casteiid01.casalelug.ch:8080/odata4/NORTHWIND.1/ViewNorthwind/EmployeesEntity(8)/deputyDelegates

      3- http://casteiid01.casalelug.ch:8080/odata4/NORTHWIND.1/ViewNorthwind/EmployeesEntity(8)/timeSheetDelegates

       

      the correponding SQL query, as reported on the logs, are always:

       

      SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.Delegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

       

      while the correct ones should be:

       

      1- SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.Delegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

      2- SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.DeputyDelegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

      3- SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.TimeSheetDelegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

       

      thanks,

      Mirco