Workaraound for missing outer apply in Oracle

Workaraound for missing outer apply in Oracle

Postby bolek75 » 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
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » 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
                   };
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bolek75 » Mon 06 Sep 2010 07:32

I have to use EntitySQL. How would you express this in EntitySQL?
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » 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
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bolek75 » 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
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » Wed 08 Sep 2010 14:10

Could you please provide a DDL script of your db objects?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bolek75 » 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
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » 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);
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bolek75 » 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".
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby bolek75 » Wed 22 Sep 2010 11:24

Can´t anybody help?
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bolek75 » 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
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58


Return to dotConnect for Oracle