Workaraound for missing outer apply in Oracle

[bolek75]

Hi folks,

I have problems expressing something like this in my EntitySQL using dotConnect for Oracle because it is resulting in a exception "Oracle is not supporting OUTER APPLY". Now I cant find an alternative and dont know what to do.

SELECT E.SurName, E.FirstName, E.Department.DeptName, (SELECT TOP(1) ED.EntryDate FROM MyEFContainer.EmployeeDepartments AS ED WHERE ED.EmplID=E.EmplID ORDER BY ED.EntryDate) FROM MyEFContainer.Employees AS E;

The scenario is as follows. An Employee belongs to min. 1 department at a given time. My query has to fetch all employees with the department they belong to for the longest time.

Let's assume I have 3 entities
Entity 1: (ID, SurName, FirstName)
Entity 2: (ID, DeptName)
Entity 3: (ID, EmplID, DeptID, EntryDate)

Cheers
bolek75

[AndreyR]

I have made a LINQ to Entities query that does the work you wish and does not produce an OUTER APPLY error at my system:

Code: Select all

var q1 = from emp in db1.Employees
                   let ed = ((from empdept in db1.Empldeparts
                              where empdept.Emplid == emp.Id
                              select empdept.Entrydate).Min())
                   select new
                   {
                     emp.Surname,
                     emp.Firstname,
                     emp.Department.Deptname,
                     EntryDate = ed
                   };

[bolek75]

I have to use EntitySQL. How would you express this in EntitySQL?

[AndreyR]

Here is the code:

Code: Select all

SELECT E.SurName, E.FirstName, E.Department.DeptName, 
          MIN(SELECT VALUE ED.EntryDate FROM Empldeparts AS ED WHERE ED.EmplID=E.ID) FROM Employees AS E

[bolek75]

Here is the code:

Code: Select all

SELECT E.SurName, E.FirstName, E.Department.DeptName, 
          MIN(SELECT VALUE ED.EntryDate FROM Empldeparts AS ED WHERE ED.EmplID=E.ID) FROM Employees AS E

Hallo AndreyR,

thanx for your help. The sub clause with MIN works fine. But how to retrieve the department name. Employee has a collections of departments not a single department reference as suggested by your SQL.

Cheers
bolek75

[AndreyR]

Could you please provide a DDL script of your db objects?

[bolek75]

Could you please provide a DDL script of your db objects?

It was just a hypothetical database. My database is much more complex.
Using the example of the Northwind database imagine the table 'Territories' owns additional column of type double 'TerritoryArea'.
My EntitySQL query has to fetch the data of employee with the ID e.g. 1 and the territory he belongs to with the largest area.

Cheers
bolek75

[AndreyR]

Try this code:

Code: Select all

SELECT e.FIRSTNAME, e.SURNAME, ed.ENTRYDATE, ed.Department.DeptName FROM EmplDeparts as Ed, Employees as E, Departments as d where ED.EmplID=E.ID AND ed.Entrydate in (select value min(em.entrydate) from EmplDeparts as em);

[bolek75]

Hallo Andrey,

Its nearly right. What I need is the employees department he himself belongs for the most time. It has to look something like this:

Code: Select all

SELECT E.ID, E.FIRSTNAME, E.SURNAME, Ed.ENTRYDATE, Ed.Department.DeptName FROM EmplDeparts as Ed, 
Employees as E, Departments as D WHERE Ed.EmplID=E.ID AND Ed.Entrydate in 
(select value min(Em.Entrydate) FROM EmplDeparts as Em WHERE Em.EmplID=E.ID)

Unfortunately I get an error "E.ID invalid identifier".

[bolek75]

Can´t anybody help?

[AndreyR]

This query works for me on Oracle 10g, but fails on Oracle 11g with the error you have specified. This is a known Oracle problem.
We will try to find some workaround.

[AndreyR]

The following solution worked for me on both Oracle 10g and Oracle 11g.
Create a stored function like the following:

Code: Select all

CREATE OR REPLACE FUNCTION SCOTT.OldestDeptsByEmplIDFunc(id NUMBER) return timestamp AS 
d timestamp;
BEGIN 
  SELECT MIN(Entrydate) into d FROM EmplDeparts WHERE EmplID = ID;
  return d;
END;
/

Add this function to the Devart Entity model. There will be an error about the composable functions, that is correct. Now you have a stored function available in SSDL.
At this point you can use this function in a query like in the following example:

Code: Select all

var q = context.CreateQuery(@"SELECT e.FIRSTNAME, e.SURNAME, ed.ENTRYDATE, ed.Department.DeptName FROM EmplDeparts as Ed, Employees as E, Departments as d where ED.EmplID=E.ID AND ed.Entrydate = Model.Store.OLDESTDEPTSBYEMPLIDFUNC(e.ID)");

Don't forget to replace the "Model.Store.OLDESTDEPTSBYEMPLIDFUNC" with the name of your storage function with the container prefix.

[bolek75]

Add this function to the Devart Entity model. There will be an error about the composable functions, that is correct. Now you have a stored function available in SSDL.
At this point you can use this function in a query like in the following example:

Using an Oracle function works great. Thanks for your support