Conversion of old style join to ansi join in oracle

Question

I have a huge sql which is written in old style join code and i am trying to change it to ansi join, i dint not paste the select query and just pasted the join condition for ease The ansi join is not producing any data, but the old one does.

Here is my old style join

 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'

i tried to convert it to ansi join and below is the code

 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)

The old code works but the latter does not , is there any thing that i am missing out on or do i have an issue with my conversion all together.

See [Outer-joins Oracle vs ANSI syntax](https://lalitkumarb.wordpress.com/2015/08/12/outer-joins-oracle-vs-ansi-syntax/) — Lalit Kumar B, Mar 18 '20 at 15:34
Does this answer your question? [Oracle "(+)" Operator](https://stackoverflow.com/questions/4020786/oracle-operator) — philipxy, Feb 10 '23 at 13:40

score 1 · Answer 1 · answered Mar 18 '20 at 13:41

1

I'm pretty sure this is the logic that you want:

 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' ;

answered Mar 18 '20 at 13:41

Gordon Linoff

1,242,037
58
646
786

yes, left join works but the below , could you let me know how to conclude if thats a left or right looking at old style code – user2256825 Mar 18 '20 at 13:46
i have read the below https://stackoverflow.com/questions/18891148/oracle-joins-comparison-between-conventional-syntax-vs-ansi-syntax and have gone for right join but is this wrong – user2256825 Mar 18 '20 at 13:46
@user2256825 . . . I don't. I am guessing based on the logic in the query. If you summarize two tables, then those are probably lookup tables for the initial tables. – Gordon Linoff Mar 18 '20 at 13:58

score 1 · Answer 2 · answered Mar 18 '20 at 13:42

1

To me, it looks like LEFT (outer) JOIN might do the job (instead of RIGHT one).

answered Mar 18 '20 at 13:42

Littlefoot

131,892
15
35
57

yes, left join works but the below , could you let me know how to conclude if thats a left or right looking at old style code – user2256825 Mar 18 '20 at 13:46
In Oracle's `(+)` outer join operator, it is placed next to table that "lacks" some rows, i.e. there are no matches in another table. Depending on how you put them into the `FROM` clause, i.e. which table is "visually" left or right, you'd use `left` or `right` outer join. In your example, if you substituted one table with another and used `right` join, you'd get the same result. Try it. – Littlefoot Mar 18 '20 at 13:56
@user2256825 See [Outer-joins Oracle vs ANSI syntax](https://lalitkumarb.wordpress.com/2015/08/12/outer-joins-oracle-vs-ansi-syntax/) – Lalit Kumar B Mar 18 '20 at 15:34

Conversion of old style join to ansi join in oracle

2 Answers2