SQL Server to Oracle - using Cross Apply with Oracle

Question

I have a function that takes primary keys and separates them with commas.

Oracle function:

create or replace function split(
  list in CHAR,
  delimiter in CHAR default ','
) 

return split_tbl as
  splitted split_tbl := split_tbl();
  i pls_integer := 0;
  list_ varchar2(32767) := list;

begin
  loop
    i := instr(list_, delimiter);
    if i > 0 then
      splitted.extend(1);
      splitted(splitted.last) := substr(list_, 1, i - 1);
      list_ := substr(list_, i + length(delimiter));
    else
      splitted.extend(1);
      splitted(splitted.last) := list_;
      return splitted;
    end if;
  end loop;
end;

and I have this query in SQL Server that returns the data of this query in the function table

select maxUserSalary.id as 'UserSalary'
into #usersalary
from dbo.Split(@usersalary,';') as userid
cross apply (
    select top 1 * from User_Salaryas usersalary
    where usersalary.User_Id= userid.item
    order by usersalary.Date desc
)  as maxUserSalary

The problem is, I'm not able to use cross apply in Oracle to throw this data into this function that is returning a table.

How can I use cross apply with Oracle to return this data in function?

So cross apply is supported. You need to change the part of `top * 1` to offset equivalent — Eldar, Jan 06 '20 at 18:07
@AllPower: then you have `CROSS APPLY` available. Please **edit your question** using the `edit` button just below the tags and include sample data for all the tables involved, the results you expect based on your test data, the results you're getting, and any errors which are being returned. Thanks. — Bob Jarvis - Слава Україні, Jan 06 '20 at 18:08

score 2 · Answer 1 · answered Jan 06 '20 at 18:31

You're using Oracle 18c so you can use the CROSS APPLY syntax. Oracle added it (as well as LATERAL and OUTER APPLY ) in 12c.

Here is a simplified version of your logic:

select us.name
       , us.salary
from table(split('FOX IN SOCKS,THING ONE,THING TWO')) t       
     cross apply (select us.name, max(us.salary) as salary 
                  from user_salaries us
                  where us.name = t.column_value ) us

There is a working demo on db<>fiddle .

If this doesn't completely solve your problem please post a complete question with table structures, sample data and expected output derived from that sample.

Instead for selecting `MAX(us.salary)` in the `CROSS APPLY`, you might get better performance just selecting `us.salary` and adding `ORDER BY us.salary DESC FETCH FIRST 1 ROW ONLY` (depending on indexes and data volumes). — Matthew McPeak, Jan 06 '20 at 18:37

score 1 · Answer 2 · answered Jan 06 '20 at 19:05

I think APC answered your direct question well. As a side note, I wanted to suggest NOT writing your own function to do this at all. There are several existing solutions to split delimited string values into virtual tables that don't require you to create your own custom types, and don't have the performance overhead of context switching between the SQL and PL/SQL engines.

-- example data - remove this to test with your User_Salary table
with User_Salary as (select 1 as id, 'A' as user_id, sysdate as "Date" from dual 
                     union select 2, 'B', sysdate from dual)
-- your query:
select maxUserSalary.id as "UserSalary"
from (select trim(COLUMN_VALUE) as item 
      from xmltable(('"'||replace(:usersalary, ';', '","')||'"'))) userid -- note ';' delimiter
cross apply (
    select * from User_Salary usersalary
    where usersalary.User_Id = userid.item
    order by usersalary."Date" desc
    fetch first 1 row only
) maxUserSalary;

If you run this and pass in 'A;B;C' for :usersalary, you'll get 1 and 2 back.

A few notes:

In this example, I'm using ; as the delimiter, since that's what your query used.
I tried to match your table/column names, but your column name Date is invalid - it's an Oracle reserved keyword, so it has to be put in quotes to be a valid column name.
As a column identifier, "UserSalary" should also have double quotes, not single.
You can't use as in table aliases.
I removed into usersalary, since into is only used with queries which return a single row, and your query can return multiple rows.

SQL Server to Oracle - using Cross Apply with Oracle

2 Answers2