oracle中旧式联接到ansi联接的转换

Question

我有一个巨大的sql，它是用老式的联接代码编写的，我正试图将其更改为ansi联接，我无法粘贴选择查询，只是简单地粘贴了联接条件ansi联接没有生成任何数据，但旧联接生成了数据。

这是我的老式加入

select *****       
from CHECKING_EXT_CASH_FLW_REP CS, OTP_ALL_REP TP_REP ,
(select M_LABEL,min(M_ALT_ID) as M_ALT_ID from ING_ALIAS_REP where M_ALT_SYS=@CtpAlternateSystem:C group by M_LABEL) CA,
(select M_LABEL,min(M_ALT_ID) as M_ALT_ID from ING_ALIAS_REP where M_ALT_SYS=@PtfAlternateSystem:C group by M_LABEL) PA
where TP_REP.M_REF_DATA=CS.M_REF_DATA
and TP_REP.M_TP_PFOLIO=CS.M_TP_PFOLIO
and TP_REP.M_NB=CS.M_NB
and CA.M_LABEL(+)=TP_REP.M_TP_CNTRPID
and PA.M_LABEL(+)=TP_REP.M_TP_PFOLIO 
and CS.M_F_OBSCOM ='N' 

我试图将其转换为ansi加入，下面是代码

select **********
from OTP_ALL_REP TP_REP  
JOIN  CHECKING_EXT_CASH_FLW_REP CS ON  (TP_REP.M_REF_DATA=CS.M_REF_DATA and TP_REP.M_TP_PFOLIO=CS.M_TP_PFOLIO and TP_REP.M_NB=CS.M_NB )
RIGHT OUTER JOIN (select M_LABEL,min(M_ALT_ID) as M_ALT_ID from ING_ALIAS_REP where M_ALT_SYS=@CtpAlternateSystem:C group by M_LABEL) CA ON( CA.M_LABEL=TP_REP.M_TP_CNTRPID )
RIGHT OUTER JOIN (select M_LABEL,min(M_ALT_ID) as M_ALT_ID from ING_ALIAS_REP where M_ALT_SYS=@PtfAlternateSystem:C group by M_LABEL) PA ON (PA.M_LABEL=TP_REP.M_TP_PFOLIO)

旧代码有效，但后者无效，是不是我遗漏了什么，或者我的转换有问题。

Answer 1

我很确定这就是您想要的逻辑：

select *****       
from OTP_ALL_REP TP_REP join
CHECKING_EXT_CASH_FLW_REP CS
on TP_REP.M_REF_DATA = CS.M_REF_DATA and
TP_REP.M_TP_PFOLIO = CS.M_TP_PFOLIO
TP_REP.M_NB = CS.M_NB left join
(select M_LABEL, min(M_ALT_ID) as M_ALT_ID
from ING_ALIAS_REP
where M_ALT_SYS = @CtpAlternateSystem:C
group by M_LABEL
) CA
on CA.M_LABEL = TP_REP.M_TP_CNTRPID left join
(select M_LABEL, min(M_ALT_ID) as M_ALT_ID
from ING_ALIAS_REP
where M_ALT_SYS = @PtfAlternateSystem:C
group by M_LABEL
) PA
on PA.M_LABEL = TP_REP.M_TP_PFOLIO
where CS.M_F_OBSCOM = 'N' ;

Answer 2

对我来说，LEFT (outer) JOIN可能会完成这项工作(而不是RIGHT(。