"ORA-01417: a table may be Outer joined to at Most one other table"

Question

I’m riding a select and I came across the following mistake:

ORA-01417: a table may be Outer joined to at Most one other table

SELECT A.SEQPESSOA, A.NUMERONF, A.NROEMPRESA, A.SEQPRODUTO, D.SEQCONTRATO, E.PERCDESCONTO, D.SEQCONTRATO, E.SEQCONTRATODESCONTO, G.SEQIDENTIFICADOR
FROM MLF_NFITEM A, MLF_NOTAFISCAL B, GE_REDEPESSOA C, MGC_CONTRATO D, MGC_CONTRATODESCONTO E, MAP_PRODUTO F, MGC_CONTRATOFAMILIA G
WHERE A.SEQAUXNOTAFISCAL = B.SEQAUXNOTAFISCAL
AND B.SEQPESSOA = C.SEQPESSOA
AND C.SEQREDE = D.SEQREDE
AND D.SEQCONTRATO = E.SEQCONTRATO
AND F.SEQPRODUTO = A.SEQPRODUTO
AND F.SEQFAMILIA = G.SEQFAMILIA(+)
AND E.SEQCONTRATODESCONTO = G.SEQIDENTIFICADOR(+)

I know you are making this mistake because I put (+) in two fields of table G. I know this because if I remove one of these (+) the select works.

But I really need to make these two associations:

AND F.SEQFAMILIA = G.SEQFAMILIA(+)  
AND E.SEQCONTRATODESCONTO = G.SEQIDENTIFICADOR(+)   

And I really need you to select bring me the records of the right table even if it does not exist in the left table, but if I put only one (+) in one of the rows, the select does not bring me all the records I want, so I really need to find a way to put the (+) in the two lines.

How can I do that?

Answer 1

try to do the following,

    AND F.SEQFAMILIA (+)= G.SEQFAMILIA
AND E.SEQCONTRATODESCONTO (+) = G.SEQIDENTIFICADOR

basically, the sign of (+) in the oracle represents the side of a relation that is optional, used to mount a left Join or right Join. The way your query was, you were bringing using the interface backwards.

Answer 2

I believe you tried to make a junction referencing more than two tables. Place the joining condition and the other condition in the WHERE clause:

SELECT A.SEQPESSOA, A.NUMERONF, A.NROEMPRESA, A.SEQPRODUTO, D.SEQCONTRATO, E.PERCDESCONTO, D.SEQCONTRATO, E.SEQCONTRATODESCONTO, G.SEQIDENTIFICADOR
FROM MLF_NFITEM A JOIN MLF_NOTAFISCAL B ON (A.SEQAUXNOTAFISCAL = B.SEQAUXNOTAFISCAL)
JOIN GE_REDEPESSOA C ON (B.SEQPESSOA = C.SEQPESSOA)
JOIN MGC_CONTRATO D ON (C.SEQREDE = D.SEQREDE)
JOIN MGC_CONTRATODESCONTO E ON (D.SEQCONTRATO = E.SEQCONTRATO)
JOIN MAP_PRODUTO F ON (F.SEQPRODUTO = A.SEQPRODUTO)
RIGTH JOIN MGC_CONTRATOFAMILIA G ON (F.SEQFAMILIA = G.SEQFAMILIA)
WHERE E.SEQCONTRATODESCONTO = G.SEQIDENTIFICADOR;