Home » SQL & PL/SQL » SQL & PL/SQL » Oracles Alternative For SQLServers Cross Apply (merged)
|
|
|
|
 Can Anyone Tell me wht is the equalent function in oracle for cross apply in sql server ? [message #303346 is a reply to message #302548] |
Thu, 28 February 2008 23:35   |
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
|
Member |
|
|
Applying a Sub-Query, Joining a Derived Table ...
I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table. Let's see if I can explain that .... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible (though variables and parameters can be referenced). For example, consider:
select A.*, b.X
from A
cross join (select B.X from B where B.Val=A.Val) b
That is not legal because A.Val is out of scope within the derived table; this is because the derived table is evaluated independently of the other tables in the SELECT. To limit the rows in table B so that B.Val = A.Val, we must do that outside of the derived table via a join or in the criteria:
select A.*, b.X
from A
cross join (select * from B) b
where A.Val = b.Val
(Of course, the above is equivalent to doing an INNER JOIN to the derived table, or just joining to the table B.)
Also, keep in mind that the scope-of-derived-tables rule isn't just for CROSS JOINS, it's for all JOINS -- CROSS, INNER, OUTER and even UNION; they all use "self-contained" derived tables.
This is in contrast to a correlated sub-query, where the parent SELECT is in scope for the sub-query; the sub-query is evaluated for each row in the query, so the other tables and columns in the SELECT are all available:
select A.*, (select B.X from B where B.Val=A.Val) as X
from A
(Note: I am ignoring for now the fact that returning multiple rows in a sub-query will return an error.)
This is an easy way to think of the difference between CROSS JOIN and CROSS APPLY. CROSS JOIN, as we saw, joins to a derived table; however, CROSS APPLY, despite looking like a JOIN, actually is applying a correlated sub-query. This imposes both the advantages of a correlated sub-query but also the performance implications.
So, we can simply rewrite our first example using CROSS APPLY like this:
select A.*, b.X
from A
cross apply (select B.X from B where B.Val=A.Val) b
Since we are performing an APPLY and not a JOIN, A.Val is in scope and it works just fine.
Table Valued User Defined Functions
Note that the same rules apply when using Table-Valued User-Defined Functions:
select A.*, B.X
from A
cross join dbo.UDF(A.Val) B
is not legal; once again, A.Val is not in scope for the user-defined function. The best we can do before SQL 2005 was to use a correlated sub-query:
select A.*, (select X from dbo.UDF(A.Val)) X
from A
However, that is not logically equivalent; the UDF cannot return more than 1 row or it will result in an error, and wouldn't make logical sense anyway if it did.
Starting with SQL 2005, we can now use CROSS APPLY and it will work fine:
HOw we can solve the issue in oracle ?
i need to cross join with user defined function?
|
|
|
|
| Re: Can Anyone Tell me wht is the equalent function in oracle for cross apply in sql server ? [message #303386 is a reply to message #303346] |
Fri, 29 February 2008 02:14 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
OK, I read through this, and in all honesty, it's still not clear what you are wanting to actually do. So, I did a google search and it appears that the CROSS APPLY is used to perform a partitioned Top N analysis i.e. find the Top x rows per grouping e.g. find the top 3 salaries per department. So there would have been my explanation right there:
CROSS APPLY will let us find the Top x rows per grouping
Really, Really easy in Oracle. You would need
Analytic Functions
Of particular use to you (I'm guessing, since you haven't really told us what you are trying to do with your data) would be:
RANK
DENSE_RANK
ROW_NUMBER
Have a read, give 'em a try and post back with successes/problems
Jim
PS, in future, when you post, can you please format any code and place it between [code]...[/code] tags. It helps a lot with readability.
|
|
|
|
|
|
| Re: Can Anyone Tell me wht is the equalent function in oracle for cross apply in sql server ? [message #303859 is a reply to message #303812] |
Mon, 03 March 2008 00:58 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you have the following code for SQL Server:
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
The Oracle equivalent would be:
SELECT *
FROM Departments D,
TABLE (fn_getsubtree(D.deptmgrid)) ST
assuming that your function returns the proper type.
If you take the code for SQL Server in the following link:
http://technet.microsoft.com/en-us/library/ms175156.aspx
and rewrite it in Oracle SQL and PL/SQL you get something like:
SCOTT@orcl_11g> set define off scan off
SCOTT@orcl_11g> CREATE TABLE Employees
2 (
3 empid int NOT NULL,
4 mgrid int NULL,
5 empname varchar2(25) NOT NULL,
6 salary NUMBER NOT NULL,
7 CONSTRAINT PK_Employees PRIMARY KEY(empid)
8 )
9 /
Table created.
SCOTT@orcl_11g> BEGIN
2 INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , 10000.00);
3 INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , 5000.00);
4 INSERT INTO Employees VALUES(3 , 1 , 'Janet' , 5000.00);
5 INSERT INTO Employees VALUES(4 , 1 , 'Margaret', 5000.00);
6 INSERT INTO Employees VALUES(5 , 2 , 'Steven' , 2500.00);
7 INSERT INTO Employees VALUES(6 , 2 , 'Michael' , 2500.00);
8 INSERT INTO Employees VALUES(7 , 3 , 'Robert' , 2500.00);
9 INSERT INTO Employees VALUES(8 , 3 , 'Laura' , 2500.00);
10 INSERT INTO Employees VALUES(9 , 3 , 'Ann' , 2500.00);
11 INSERT INTO Employees VALUES(10, 4 , 'Ina' , 2500.00);
12 INSERT INTO Employees VALUES(11, 7 , 'David' , 2000.00);
13 INSERT INTO Employees VALUES(12, 7 , 'Ron' , 2000.00);
14 INSERT INTO Employees VALUES(13, 7 , 'Dan' , 2000.00);
15 INSERT INTO Employees VALUES(14, 11 , 'James' , 1500.00);
16 END;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> CREATE TABLE Departments
2 (
3 deptid INT NOT NULL PRIMARY KEY,
4 deptname VARCHAR(25) NOT NULL,
5 deptmgrid INT NULL REFERENCES Employees
6 )
7 /
Table created.
SCOTT@orcl_11g> BEGIN
2 INSERT INTO Departments VALUES(1, 'HR', 2);
3 INSERT INTO Departments VALUES(2, 'Marketing', 7);
4 INSERT INTO Departments VALUES(3, 'Finance', 8);
5 INSERT INTO Departments VALUES(4, 'R&D', 9);
6 INSERT INTO Departments VALUES(5, 'Training', 4);
7 INSERT INTO Departments VALUES(6, 'Gardening', NULL);
8 END;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE tree_typ AS OBJECT
2 (empid INT,
3 empname VARCHAR2(25),
4 mgrid INT,
5 lvl INT);
6 /
Type created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE tree_tab AS TABLE OF tree_typ;
2 /
Type created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION fn_getsubtree
2 (p_empid IN INT)
3 RETURN tree_tab
4 AS
5 v_tree tree_tab := tree_tab();
6 BEGIN
7 FOR r IN
8 (SELECT empid, empname, mgrid, LEVEL - 1 AS lvl
9 FROM employees
10 START WITH empid = p_empid
11 CONNECT BY PRIOR empid = mgrid)
12 LOOP
13 v_tree.EXTEND;
14 v_tree(v_tree.COUNT) := tree_typ (r.empid, r.empname, r.mgrid, r.lvl);
15 END LOOP;
16 RETURN v_tree;
17 END fn_getsubtree;
18 /
Function created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT *
2 FROM Departments D,
3 TABLE (fn_getsubtree(D.deptmgrid)) ST
4 ORDER BY deptid, empid
5 /
DEPTID DEPTNAME DEPTMGRID EMPID EMPNAME MGRID LVL
---------- ------------------------- ---------- ---------- ------------------------- ---------- ----------
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
12 rows selected.
SCOTT@orcl_11g>
This is not necessarily the most efficient method in Oracle. It is just a reproduction of the example for comparison of syntax. In Oracle, instead of using a function, it would probably be more efficient to do it in one SQL query, like so:
SCOTT@orcl_11g> SELECT *
2 FROM Departments D,
3 TABLE (CAST (MULTISET (SELECT empid, empname, mgrid, LEVEL - 1 AS lvl
4 FROM employees
5 START WITH empid = D.deptmgrid
6 CONNECT BY PRIOR empid = mgrid) AS tree_tab)) ST
7 ORDER BY deptid, empid
8 /
DEPTID DEPTNAME DEPTMGRID EMPID EMPNAME MGRID LVL
---------- ------------------------- ---------- ---------- ------------------------- ---------- ----------
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
12 rows selected.
SCOTT@orcl_11g>
[Updated on: Mon, 03 March 2008 02:18] Report message to a moderator |
|
|
|
| Re: Can Anyone Tell me wht is the equalent function in oracle for cross apply in sql server ? [message #304917 is a reply to message #303859] |
Fri, 07 March 2008 02:55 |
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
|
Member |
|
|
I Have used the table Function In My Sql But it is taking 5 min to Execute 9 rows ? I am facing Performance issue In That how to get the performance any solution?
pls Help
SELECT DISTINCT
'Report Profile',
--fa.field_attribute_id,
rp.instance,
'Field Attribute',
fa.field_attribute_id
FROM
pace_master..report_profiles rp,
rules.dbo.field_attributes fa,
table(pace_masterdbo.mdm_split(rp.user_data,',')) all_tags,
table(pace_master.dbo.mdm_split(all_tags.val,'~')) fd_tags
WHERE
fa.field_attribute_id = pace_masterdbo.get_split_value(fd_tags.val,'DATE_FIELD!')
AND fa.field_attribute_id != -1
AND all_tags.val IS NOT NULL
AND fd_tags.val IS NOT NULL
AND all_tags.val LIKE '%DATE_FIELD!%'
AND fd_tags.val LIKE '%DATE_FIELD!%'
|
|
|
|
|
|
| Re: Can Anyone Tell me wht is the equalent function in oracle for cross apply in sql server ? [message #430202 is a reply to message #303859] |
Mon, 09 November 2009 07:58 |
ta.bu.shi.da.yu
Messages: 1
Registered: November 2009
Location: Sydney, Australia
|
Junior Member |
|
|
Hello, I know that I'm replying to a reasonably stale thread, but I read Barbara's response with great interest - it appears that there are some very cool things that you can do with objects in Oracle and I'm going to have a read up on them very soon.
Just for people's information, in SQL Server 2005 and above you are not forced to use a function and you can use a derived table... it's just that most people use functions for some reason.
Sorry... I know that it's an Oracle forum, but someone else like myself might stumble across it.
[Updated on: Mon, 09 November 2009 07:58] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue May 20 06:25:33 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
