ORA-01799: a column may not be Outer-joined to a subquery

Question

I am having a problem with oracle, it is not accepting a subquery in a left Join, if I change to Inner Join works, but changes the result of my select so it does not meet the solution. the problem happens with the following example:

    CREATE TABLE A (ID INT, NOME VARCHAR2(50))
    CREATE TABLE B (ID INT, ID_A INT, NUMERO NUMBER(8))

Query:

    SELECT A.NOME
    FROM A LEFT JOIN B
    ON A.ID = B.ID_A AND B.NUMERO = (SELECT MAX(NUMERO) FROM B B2 WHERE B2.ID_A = A.ID)

I need to rewrite in a way that the oracle accepts keeping as left Join.

Answer 1

You can do it this way:

SELECT A.NOME
  FROM A 
       LEFT JOIN (SELECT B2.ID_A,
                         B2.OUTRA_COLUNA
                    FROM B B2
                   WHERE B2.NUMERO = (SELECT MAX(B3.NUMERO)
                                        FROM B B3
                                       WHERE B3.ID = B2.ID
                                     )
                 ) B ON (A.ID = B.ID_A)