0

I am trying to convert a code which is written in SQL to oracle and it is using outer apply in it. But for some reason the outer apply is not working in oracle. I am using oracle 12c. The code which i am converting is:

select * from FROM table1 S1
OUTER APPLY (VALUES (S1.FM46B_PROV_NET_SPEC_P, '1'),
                    (S1.PROV_NET_SPEC_1,'2'),
                    (S1.PROV_NET_SPEC_2,'3'),
                    (S1.PROV_NET_SPEC_3,'4')) PS(PROV_SPEC, SpecType)

I did write the code in oracle using left join and union but it is not working. Could someone please advise me on what needs to be done in-order to write the code using outer apply or using some other way.

Thanks in advance.

Aswin Ajai
  • 11
  • 2

2 Answers2

0

Use select from dual:

select * 
from table1 S1 outer apply
     (select S1.FM46B_PROV_NET_SPEC_P as prev_spec, '1' as spectype from dual union all
      select S1.PROV_NET_SPEC_1, '2' from dual union all
      select S1.PROV_NET_SPEC_2, '3' from dual union all
      select S1.PROV_NET_SPEC_3, '4' from dual
     ) PS;

I would use the equivalent cross join lateral instead of outer apply to be explicit about the type of join, but both are supported.

In any case, Oracle provided support for lateral joins, but did not provide support for the derived tables using values.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If you just want to UNPIVOT the columns to rows then:

select *
FROM   table1
UNPIVOT (
  prov_spec FOR spec_type IN (
    FM46B_PROV_NET_SPEC_P AS '1',
    PROV_NET_SPEC_1 AS '2',
    PROV_NET_SPEC_2 AS '3',
    PROV_NET_SPEC_3 AS '4' 
  )
);

Which, for the sample data:

CREATE TABLE table1 ( id, FM46B_PROV_NET_SPEC_P, PROV_NET_SPEC_1, PROV_NET_SPEC_2, PROV_NET_SPEC_3 ) AS
SELECT 1, '1.spec.p', '1.spec.1', '1.spec.2', '1.spec.3' FROM DUAL UNION ALL
SELECT 2, '2.spec.p', '2.spec.1', '2.spec.2', '2.spec.3' FROM DUAL;

Outputs:

ID | SPEC_TYPE | PROV_SPEC
-: | :-------- | :--------
 1 | 1         | 1.spec.p 
 1 | 2         | 1.spec.1 
 1 | 3         | 1.spec.2 
 1 | 4         | 1.spec.3 
 2 | 1         | 2.spec.p 
 2 | 2         | 2.spec.1 
 2 | 3         | 2.spec.2 
 2 | 4         | 2.spec.3

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Hello MT0. The solution that i wanted was more like similar to outer apply and the answer from Gordon actually helped. Anyways, thank you for your time :) – Aswin Ajai Nov 19 '20 at 09:49
  • @MT0 . . . You do know that `unpivot` is not equivalent to a lateral join in this case because unpivot filters out `NULL` values. – Gordon Linoff Nov 19 '20 at 11:59
  • @GordonLinoff Then use the `INCLUDE NULLS` clause to include them. – MT0 Nov 19 '20 at 12:04