SQL Server 2005 provides a CROSS APPLY clause which in Oracle terms allows a correlated inline view or table function.
Heres some SQL Server CROSS APPLY background.
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/10/18/sql-server-cross-apply.aspxThere are a couple of ways to convert this based on what exactly is trying to be done.
But here are a few methods.
There are 2 main uses of CROSS APPLY
1) CROSS APPLY with a table function
2) CROSS APPLY with a subquery (inline view in Oracleze)
1) CROSS APPLY with a table function
Jeff Smith put together this neat example of using a table function with CROSS APPLY
http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply
I expanded this example to try out a few different scenarios
drop table emails
go
create table emails (ID int primary key, EmailAddress varchar(100))
go
--added 2 rows which cant be processed by EmailParse
insert into emails
select 1,'test1@gmail.com' union all
select 2,'test2@gmail.com' union all
select 3,'test3@gmail.com' union all
select 4,'test' union all
select 5, null
go
drop function EmailParse1
go
--return a table with (null,null) if you cant process
create function EmailParse1 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
declare @i int
select @i = charindex('@', @email,1);
if (@i > 1)
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
else
insert into @t values (null,null)
return
end
go
drop function EmailParse2
go
--return nothing if you cant process
create function EmailParse2 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
declare @i int
select @i = charindex('@', @email,1);
if (@i > 1)
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
--return nothing if not valid
return
end
go
drop function EmailParse3
go
--return multiple rows if you can process and nothing if you cant
create function EmailParse3 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
declare @i int
select @i = charindex('@', @email,1);
if (@i > 1)
begin
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
end
return
end
go
drop function EmailParse4
go
--return multiple rows if you can process and a table(null,null) if you cant
create function EmailParse4 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
declare @i int
select @i = charindex('@', @email,1);
if (@i > 1)
begin
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
end
else
insert into @t values (null,null)
return
end
go
PRINT 'SELECT 1'
go
select * from emails cross apply EmailParse1(emails.EmailAddress) s
go
PRINT 'SELECT 2'
go
select * from emails outer apply EmailParse1(emails.EmailAddress) s
go
PRINT 'SELECT 3'
go
select * from emails cross apply EmailParse2(emails.EmailAddress) s
go
PRINT 'SELECT 4'
go
select * from emails outer apply EmailParse2(emails.EmailAddress) s
go
PRINT 'SELECT 5'
go
select * from emails cross apply EmailParse3(emails.EmailAddress) s
go
PRINT 'SELECT 6'
go
select * from emails outer apply EmailParse3(emails.EmailAddress) s
go
PRINT 'SELECT 7'
go
select * from emails cross apply EmailParse4(emails.EmailAddress) s
go
PRINT 'SELECT 8'
go
select * from emails outer apply EmailParse4(emails.EmailAddress) s
go
Generic Oracle PL/SQL Solution
DROP TABLE emails;
CREATE TABLE emails(id int, emailaddress varchar2(100));
INSERT INTO emails VALUES(1,'test1@gmail.com');
INSERT INTO emails VALUES(2,'test2@gmail.com');
INSERT INTO emails VALUES(3,'test3@gmail.com');
INSERT INTO emails VALUES(4,'test');
INSERT INTO emails VALUES(5,null);
DROP PACKAGE Email_pkg;
/
DROP TYPE emailAddressObject;
/
CREATE OR REPLACE TYPE emailAddressObject AS OBJECT
(UserName VARCHAR2(20) , Domain VARCHAR2(20),status CHAR(1));
/
CREATE OR REPLACE PACKAGE Email_pkg
AS
TYPE emailAddressTable IS TABLE OF emailAddressObject;
END;
/
CREATE OR REPLACE FUNCTION EmailParse1(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
v_i := INSTR(v_email, '@', 1);
IF ( v_i > 1 ) THEN
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' ));
ELSE
PIPE ROW(emailAddressObject(null,null,'P')); -- This needs to pass as this null table was present in the original
END IF;
RETURN;
END;
/
CREATE OR REPLACE FUNCTION EmailParse2(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
v_i := INSTR(v_email, '@', 1);
IF ( v_i > 1 ) THEN
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' ));
ELSE -- ADDED IN TO EMULATE
PIPE ROW(emailAddressObject(null,null,null));
END IF;
RETURN;
END;
/
CREATE OR REPLACE FUNCTION EmailParse3(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
v_i := INSTR(v_email, '@', 1);
IF ( v_i > 1 ) THEN
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P'));
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P')); --multirow
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P')); --multirow
ELSE -- ADDED IN TO EMULATE
PIPE ROW(emailAddressObject(null,null,null));
END IF;
RETURN;
END;
/
CREATE OR REPLACE FUNCTION EmailParse4(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
v_i := INSTR(v_email, '@', 1);
IF ( v_i > 1 ) THEN
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' ));
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' )); --multirow
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' )); --multirow
ELSE
PIPE ROW(emailAddressObject(null,null,'P')); -- This needs to pass as this null table was present in the original
END IF;
RETURN;
END;
/
PROMPT SELECT 1
--SELECT * FROM emails CROSS JOIN TABLE(EmailParse1(emails.emailaddress));
SELECT * FROM emails INNER JOIN TABLE(EmailParse1(emails.emailaddress))s ON s.status='P';
PROMPT SELECT 2
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse1(emails.emailaddress))s ON 1=1;
PROMPT SELECT 3
SELECT * FROM emails INNER JOIN TABLE(EmailParse2(emails.emailaddress))s ON s.status ='P';
PROMPT SELECT 4
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse2(emails.emailaddress)) ON 1=1;
PROMPT SELECT 5
SELECT * FROM emails INNER JOIN TABLE(EmailParse3(emails.emailaddress))s ON s.status = 'P';
PROMPT SELECT 6
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse3(emails.emailaddress)) ON 1=1;
PROMPT SELECT 7
SELECT * FROM emails INNER JOIN TABLE(EmailParse4(emails.emailaddress))s ON s.status = 'P';
PROMPT SELECT 8
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse4(emails.emailaddress)) ON 1=1;
Explanation:The EmailParse functions get converted to Oracle pipelined table function.
If the SQL Server table function always returns a table under all circumstances
insert into @t values (null,null) --see example EmailParse1, EmailParse4
the conversion to Oracle is relatively straight forward.
You can convert the
SQL Server CROSS APPLY to Oracle CROSS JOIN
And
SQL Server OUTER APPLY to Oracle LEFT OUTER JOIN ... ON 1=1
Heres the example just converting EmailParse1, EmailParse4 as they always return a table.
DROP TABLE emails;
CREATE TABLE emails(id int, emailaddress varchar2(100));
INSERT INTO emails VALUES(1,'test1@gmail.com');
INSERT INTO emails VALUES(2,'test2@gmail.com');
INSERT INTO emails VALUES(3,'test3@gmail.com');
INSERT INTO emails VALUES(4,'test');
INSERT INTO emails VALUES(5,null);
DROP PACKAGE Email_pkg;
/
DROP TYPE emailAddressObject;
/
CREATE OR REPLACE TYPE emailAddressObject AS OBJECT
(UserName VARCHAR2(20) , Domain VARCHAR2(20));
/
CREATE OR REPLACE PACKAGE Email_pkg
AS
TYPE emailAddressTable IS TABLE OF emailAddressObject;
END;
/
CREATE OR REPLACE FUNCTION EmailParse1(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
v_i := INSTR(v_email, '@', 1);
IF ( v_i > 1 ) THEN
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) ));
ELSE
PIPE ROW(emailAddressObject(null,null)); -- This needs to pass as this null table was present in the original
END IF;
RETURN;
END;
/
CREATE OR REPLACE FUNCTION EmailParse4(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
v_i := INSTR(v_email, '@', 1);
IF ( v_i > 1 ) THEN
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) ));
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) )); --multirow
PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) )); --multirow
ELSE
PIPE ROW(emailAddressObject(null,null)); -- This needs to pass as this null table was present in the original
END IF;
RETURN;
END;
/
PROMPT SELECT 1
--SELECT * FROM emails CROSS JOIN TABLE(EmailParse1(emails.emailaddress));
SELECT * FROM emails CROSS JOIN TABLE(EmailParse1(emails.emailaddress))s ;
PROMPT SELECT 2
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse1(emails.emailaddress))s ON 1=1;
PROMPT SELECT 7
SELECT * FROM emails CROSS JOIN TABLE(EmailParse4(emails.emailaddress))s;
PROMPT SELECT 8
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse4(emails.emailaddress)) ON 1=1;
If the SQL Server table function doesn't always return a function
For example in EmailParse2, EmailsParse3
It more difficult to convert to Oracle. The reason being there is a difference as to how SQL Server and Oracle interpret Table Functions when nothing is returned.
The conversion require is more complex, but it will work in both scenarios so is a generic solution. If performing the conversion by hand then each query maybe modified to suit its own circumstances.
In the above (generic) example the SQL Server EmailParse2 and EmailParse3 return nothing if it cant parse the email address. The last two values in emails cant be parsed ('dermot' and null).
In SQL Server if nothing is returned by the table function then
A) The Cross Apply happens and those null rows are restricted
B) The Outer Apply happens and all the left hand side table rows are used.
In Oracle if nothing is returned by the table function then the CAST to TABLE doesnt happen. This is the difference between Oracle and SQL Server.
A) The Oracle CROSS JOIN happens and those null rows are restricted. Same as SQL Server
B) The Oracle LEFT OUTER JOIN happens but as there is no right hand side table, the join fails. Restricting those rows in the left hand side table. This is different from SQL Server, which returns all the rows in the left hand side table.
So you may think that the solution is to make sure the Oracle always returns a table, even emailAddressObject(null,null).
Now the Oracle LEFT OUTER JOIN happens identically to SQL Server as there is a table to join against. But the Oracle CROSS JOIN returns all the rows, it doesn't restrict null values as it thinks it has a result (an empty table) to join with.
So we need to limit the Oracle CROSS JOIN to only those rows which are successfully parsed. This could entail adding another field to the emailAddressObject with a status that can be checked.
And changing the CROSS JOIN to
INNER JOIN .. ON emailAddressObject.status ='P' -- 'P' for passed
The conversion steps are
1) Convert The SQL Server Table Function to an Oracle Pipelined Table function
2) Create an Oracle Oracle TYPE
CREATE OR REPLACE TYPE emailAddressObject AS OBJECT(UserName VARCHAR2(20) , Domain VARCHAR2(20),status CHAR(1));
Notice the status field is new. This will appear in a select list if using SELECT *
3)Make sure all Oracle Table Function return a table in all cases.
If nothing was returned before pass back
PIPE ROW(emailAddressObject(null,null,null));If a null table was being passed back in SQL Server
(insert into @t values (null,null)) return the following in Oracle.
PIPE ROW(emailAddressObject(null,null,'P'));4)SQL Server CROSS APPLY converts to Oracle INNER JOIN .... ON s.status='P'
5)SQL Server OUTER APPLY converts to Oracle LEFT OUTER JOIN .... ON 1 = 1
CROSS APPLY with a subquery (inline view in Oracleze)Oracle inline views cannot reference outer queries, they are effectively standalone and are instantiated inmemory before the outer query is processed.
Heres a T-SQL Example that uses an inline view, based on Jeff Smith's example.
drop table customer
go
create table customer (emailid int, city varchar(100), country varchar(100))
go
insert into customer
select 1,'new york','usa' union all
select 2,'dublin','ireland' union all
select 3,'hong kong', 'china'
go
select emails.ID, s.city, s.country
from emails
CROSS APPLY
(select city,country from customer where emailid =emails.ID ) s;
go
select emails.ID, s.city, s.country
from emails
OUTER APPLY
(select city,country from customer where emailid =emails.ID ) s;
go
Oracle wont allow the inline view ("s") to reference the emails table in the outer query. So there are a few ways to convert this.
Oracle PL/SQL
drop table customer;
/
create table customer (emailid int, city varchar2(100), country varchar2(100));
/
insert into customer
select 1,'new york','usa' from dual union all
select 2,'dublin','ireland' from dual union all
select 3,'hong kong', 'china' from dual ;
/
select emails.ID, s.city, s.country
from emails
INNER JOIN (select city,country,emailid from customer ) s
ON s.emailid =emails.ID;
/
select emails.ID, s.city, s.country
from emails
LEFT OUTER JOIN
(select city,country,emailid from customer) s
ON s.emailid =emails.ID;
/
The T-SQL CROSS APPLY is converted to PL/SQL using an INNER JOIN with the correlation is pulled out as the ON condition.
The T-SQL OUTER APPLY is converted to PL/SQL using a LEFT OUTER JOIN, again the correlation is pulled out as the ON condition.
Note that the inline view select list was modified to include any references in the ON clause.
