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

Question

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

Navigation

#1 by (1 votes)

1

I am having a problem with oracle, it is not accepting a subquery in a left join, if I change to inner join it works, but it changes the result of my select then does not answer 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 oracle accepts keeping as a left join.

oracle pl-sql

asked by anonymous 15.04.2016 / 15:59

1 answer

While Loop in MIPS Assembly Using Grid, Windows Phone C # Silverlight?

score 1 · Answer 1

You can do this as follows:

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)