Column outer joined in subquery (ORA-01799)

Question

Oracle 11g R2:

Table:

CREATE TABLE TABLE_1 
   (REC_ID NUMBER(10,0) NOT NULL,
    REV_ID NUMBER(10,0) NOT NULL ENABLE
   );
   
INSERT ALL
INTO TABLE_1 (REC_ID,REV_ID) 
VALUES (1,23)
INTO TABLE_1 (REC_ID,REV_ID) 
VALUES (1,36)
INTO TABLE_1 (REC_ID,REV_ID) 
VALUES (1,52)
INTO TABLE_1 (REC_ID,REV_ID) 
VALUES (2,19)
INTO TABLE_1 (REC_ID,REV_ID) 
VALUES (2,67)
INTO TABLE_1 (REC_ID,REV_ID) 
VALUES (2,98)
SELECT * FROM dual;

CREATE TABLE TABLE_2
   (REC_ID NUMBER(20,0) NOT NULL ENABLE
   ); 
     
INSERT ALL
INTO TABLE_2 (REC_ID) 
VALUES (1)
INTO TABLE_2 (REC_ID) 
VALUES (2)
SELECT * FROM dual;

I want to join Table 2 with Table 1 but only where Table 1 REV_ID is the minimum REV_ID for a certain REC_ID.

+--------+--------+--------+
| REC_ID | REC_ID | REV_ID |
+--------+--------+--------+
|      1 |      1 |     23 |
|      2 |      2 |     19 |
+--------+--------+--------+

This Select:

SELECT * from TABLE_2 T2
LEFT OUTER JOIN TABLE_1 T1 on T1.REC_ID = T2.REC_ID 
and T1.REV_ID = (SELECT MIN(REV_ID) from T1 where T1.REC_ID = T2.REC_ID)

works on MSSQL Server 2017 but raises the error:

ORA-01799: a column may not be outer-joined to a subquery in oracle.

SQL Fiddle: LINK

Question: How can I rewrite the query so that the join works in all oracle versions?

Answer 1

You may use CTE with row_number to classify the row with the minimal REV_ID and the outer join only to those rows.

The advantage is this approach works even if you want some other attributes from T1

with t1_min as
(select REC_ID, REV_ID,
row_number() over (partition by REC_ID order by  REV_ID) as rn
from TABLE_1)
SELECT t2.rec_id t2_rec_id,t1.rec_id t1_rec_id,
t1.rev_id
from TABLE_2 T2
LEFT OUTER JOIN t1_min T1 on T1.REC_ID = T2.REC_ID 
and t1.rn = 1

 T2_REC_ID  T1_REC_ID     REV_ID
---------- ---------- ----------
         1          1         23
         2          2         19