Subselect in Oracle bank

Question

I am trying to elaborate a query and within one of my joins it is necessary to be made a subselect to ensure the integrity of the query as 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 "ORA-01799: a column cannot be externally connected to a sub-consumption"

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

Answer 1

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 clause where

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)

Answer 2

A subconsulta can return more than one record, so the operator should not be used = and yes the in.

A different way to do this consultation is:

select a.teste
  from teste a,
       tes_teste_2 b
 where a.isn_teste = b.isn_teste
   and b.isn_pessoa in ( select min(c.isn_pessoa)
                           from tes_teste c
                          where c.isn_teste = a.isn_teste )
   and a.isn_empresa = 666;