Convert OUTER APPLY To Oracle 11g statement

Question

I have the following TSQL,

select 2*EC.Total C1, T1.C2, EC.Total
FROM MyTable1 T1 
OUTER APPLY (select COUNT(*) AS Total from MyTable2 T2 WHERE T1.SomeColumn=T2.SomeColumn ) EC
ORDER BY EC.Total desc

I need to convert this to Oracle 11g. I tried lateral but not supported.

I cannot access T1 inside cross join? because it's saying invalid identifier — Imran Qadir Baksh - Baloch, Mar 17 '20 at 15:22

score 2 · Accepted Answer · answered Mar 17 '20 at 15:59

You don't really need a lateral join there:

select 2*EC.Total C1, T1.C2, EC.Total
FROM MyTable1 T1 
  LEFT JOIN (
    select m2.somecolumn, COUNT(*) AS Total 
    from MyTable2 m2
    group by m2.somecolumn
  ) ec ON t1.somecolumn = ec.somecolumn
ORDER BY EC.Total desc

Convert OUTER APPLY To Oracle 11g statement

1 Answers1