Home » SQL & PL/SQL » SQL & PL/SQL » Oracles Alternative For SQLServers Cross Apply (merged)
icon5.gif  Oracles Alternative For SQLServers Cross Apply (merged) [message #302548] Tue, 26 February 2008 02:18 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
I am Using Cross apply In SQL Server . I want to use the same function in Oracle. could any one help me to get alternate for this CROSS APPLY .......

Re: Alternate For Cross Apply [message #302549 is a reply to message #302548] Tue, 26 February 2008 02:20 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Do you think that maybe, it might be an idea to tell us what Cross apply does? Or should we just guess? Or do you expect us to do your work for you and look it up?
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2813
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 #303812 is a reply to message #303386] Sun, 02 March 2008 23:10 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
See now we are doing migration of SQL Server To Oracle . i need to migrate the stored procedure in that they are used one fuction called Cross Apply . It is also like join

EX: 1


select A.*, b.X
from A
cross join (select B.X from B where B.Val=A.Val) b


Ex: 2

select A.*, b.X
from A
cross join (select * from B) b
where A.Val = b.Val



Ex : 3
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

see in this ex 1 is not a valid stmt

so we are using the same in Ex 2 to give the scope to the variable

but in another way we can use cross join in Ex1 so that it will become valid stmt


This cross apply is not available in Oracle . so i need the equalent Function For Cross apply In Oracle .


For Your Ref I have enclosed the SQL Server Script Which Need to be convert into oracle....


Thanking You .
  • Attachment: funcs.sql
    (Size: 14.03KB, Downloaded 284 times)
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8587
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 Go to previous messageGo to next message
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 #304996 is a reply to message #304917] Fri, 07 March 2008 08:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8587
Registered: November 2002
Location: California, USA
Senior Member
Are you trying to run this on SQL Server or MySQL or Oracle? If you are trying to run it on SQL Server or MySQL, then you are in the wrong place and need to find a forum for that product. If you are running this on Oracle, then you need to describe the problem thoroughly. You need to provide create table and insert statements for sample data and the results that you want based on that data and why. Please read and follow the forum guide that his highlighted in yellow at the top of the forum page. We would also need to see the contents of the functions as there may be a simpler method. You also need to make sure that you have indexes on appropriate columns and current statistics to enable the optimizer to choose the best execution plan. Things like DISTINCT and LIKE tend to slow things down. If we could have an idea what you are trying to accomplish, there is probably a better way. Always we need to know your Oracle version, edition, and such. Please provide the results of the following query:

select banner from v$version;
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 Go to previous message
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

Previous Topic: sql query
Next Topic: ORA-01779: cannot modify a column which maps to a non key-preserved table
Goto Forum:
  


Current Time: Sat Oct 01 02:27:52 CDT 2016

Total time taken to generate the page: 0.10784 seconds