Consulta SQL

[MiroParticipante]

nao estou conseguindo fazer dentro de uma sub-consulta segue o script

select *
FROM
R034FUN,
R010SIT,
R034CON,
r016orn,
r030emp

      WHERE r034fun.numemp IN (1,2,5) AND
            R034FUN.TIPCOL = 1
            and r034fun.numcad = 3
            and r034con.numemp = r034fun.numemp
            and r034con.tipcol = r034fun.tipcol
            and r034con.numcad = r034fun.numcad 
            and r034fun.numemp = r030emp.numemp
            and r034fun.taborg = r016orn.taborg
            and r034fun.numloc = r016orn.numloc
            and

R034FUN.SITAFA = R010SIT.CODSIT AND

R034FUN.DATADM <= ('&data') AND 

     EXISTS(SELECT 1 FROM R038HFI 
           WHERE R038HFI.NUMEMP = R034FUN.NUMEMP AND 
                 R038HFI.TIPCOL = R034FUN.TIPCOL AND 
                 R038HFI.NUMCAD = R034FUN.NUMCAD AND 
                 R038HFI.DATALT <= ('&data')) AND 

                 R034CON.DATALT = (SELECT MAX (DATALT) 
                        FROM R034CON TABELA001
                           WHERE TABELA001.NUMEMP = R034CON.NUMEMP(+) AND 
                                 TABELA001.TIPCOL = R034CON.TIPCOL(+) AND 
                                 TABELA001.NUMCAD = R034CON.NUMCAD(+) AND 
                                 TABELA001.DATALT <= ('&data')) AND 

     NOT EXISTS (SELECT 1 FROM R038AFA 
           WHERE R038AFA.NUMEMP = R034FUN.NUMEMP AND 
                 R038AFA.TIPCOL = R034FUN.TIPCOL AND 
                 R038AFA.NUMCAD = R034FUN.NUMCAD AND 
                 R038AFA.DATAFA = (SELECT MAX(R038AFA.DATAFA) 
                        FROM R038AFA, R010SIT 
                           WHERE R038AFA.NUMEMP = R034FUN.NUMEMP AND 
                                 R038AFA.TIPCOL = R034FUN.TIPCOL AND 
                                 R038AFA.NUMCAD = R034FUN.NUMCAD AND 
                                 R038AFA.DATAFA <= ('&data') AND 
                                 R038AFA.SITAFA = R010SIT.CODSIT AND 
                                 R010SIT.TIPSIT = 7))

[quote="Litz":12438eir]Faça assim, utilize um left join

select a.numcad, b.codcon
from tabela.a, tabela.b
and a.numemp = b.numemp(+)
and a.tipcol = b.tipcol(+)
and a.numcad = b.numcad(+)

Vlw Leonardo Litz[/quote]