sql – Subselect em banco Oracle

Question:

I'm trying to prepare a query and within one of my joins a subselect needs to be done to ensure the integrity of the query, like the example below:

select t01.teste           
from teste t01                
left join tes_teste_2 t02                   
on t01.isn_teste = t02.isn_teste           
and t02.isn_pessoa = (select min(t04.isn_pessoa) from tes_teste t04 
where t04.isn_teste = t01.isn_teste)
where t01.isn_empresa = 666

The problem that returns to me that " ORA-01799: a column cannot be externally joined to a subquery "

I would like some help as I could elaborate this subselect as a condition. Emphasizing that we use Oracle 11G.

Answer:

An alternative would be

select t01.teste           
from teste t01
inner join 
(
   select isn_teste, min(isn_pessoa) isn_pessoa
   from   tes_teste 
   group by isn_teste
) t04         
  on t04.isn_teste = t01.isn_teste      
left join tes_teste_2 t02                   
  on t01.isn_teste = t02.isn_teste           
 and t02.isn_pessoa = t04.isn_pessoa
where t01.isn_empresa = 666

Another alternative would be to pass the condition to the where clause.

select t01.teste           
from teste t01                
inner join tes_teste_2 t02                   
on t01.isn_teste = t02.isn_teste           
where t01.isn_empresa = 666
  and t02.isn_pessoa = (
         select min(t04.isn_pessoa) 
           from tes_teste t04 
          where t04.isn_teste = t01.isn_teste)
Scroll to Top