Workaraound for missing outer apply in Oracle
Workaraound for missing outer apply in Oracle
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
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
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
};
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,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
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
It was just a hypothetical database. My database is much more complex.AndreyR wrote:Could you please provide a DDL script of your db objects?
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
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);
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:
Unfortunately I get an error "E.ID invalid identifier".
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)
The following solution worked for me on both Oracle 10g and Oracle 11g.
Create a stored function like the following:
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:
Don't forget to replace the "Model.Store.OLDESTDEPTSBYEMPLIDFUNC" with the name of your storage function with the container prefix.
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;
/
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)");
Using an Oracle function works great. Thanks for your supportAndreyR 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: