Devart Forums

Posted: **Thu 02 Sep 2010 09:32**

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

Posted: **Fri 03 Sep 2010 14:06**

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
                   };

Posted: **Mon 06 Sep 2010 07:32**

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

Posted: **Tue 07 Sep 2010 13:49**

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

Posted: **Wed 08 Sep 2010 13:44**

AndreyR wrote: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

Posted: **Wed 08 Sep 2010 14:10**

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

Posted: **Thu 09 Sep 2010 05:47**

AndreyR wrote: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

Posted: **Tue 14 Sep 2010 12:01**

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);

Posted: **Tue 14 Sep 2010 13:55**

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".

Posted: **Wed 22 Sep 2010 11:24**

Can´t anybody help?

Posted: **Fri 24 Sep 2010 15:41**

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.

Posted: **Wed 29 Sep 2010 11:28**

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.

Posted: **Thu 14 Oct 2010 07:02**

AndreyR wrote: 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

Devart Forums

Workaraound for missing outer apply in Oracle

Workaraound for missing outer apply in Oracle