Converting old SQL Server outer join syntax

Question

I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well.

I get a invalid expression error when I run the procedures in Power Builder. We have previously been using a lower database compatibility model in 2005, which allowed the procedures to work there. I have learned that =* is the old way to write right outer joins.

For example:

select  *
from    A
right outer join
        B
on      A.bid = B.id

...is written in the old style like:

select  *
from    A,
        B
where   A.bid =* B.id

Here is a part of my procedure that uses *=:

SELECT  DISTINCT  
   a_stmt.cyc_dte,   
   a_carr.carr_nm,   
   a_stmt.amt_du_ic  
 FROM  a_stmt,   
   s_dataccs,   
   a_carr,  
   a_icc   
 WHERE ( a_stmt.ic_cntct_id *= a_icc.ic_cntct_id ) and  -- HERE
   ( a_icc.usg_ind = 'S' ) and   
   ( a_carr.acna = a_stmt.acna ) and     
   ( s_dataccs.acna = a_stmt.acna ) and ( s_dataccs.user_id = @user_id ) and   
   ( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND    
   ( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND    
   ( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND  
   ( a_stmt.amt_du_ic < 0 ) 

I have converted that to:

SELECT  DISTINCT  
   a_stmt.cyc_dte,   
   a_carr.carr_nm,   
   a_stmt.amt_du_ic  
from
 a_stmt left outer join a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id 
 join  a_carr on a_stmt.acna = a_carr.acna join s_dataccs on a_stmt.acna = s_dataccs.acna and
   ( a_icc.usg_ind = 'S' ) and   
   ( s_dataccs.user_id = @user_id ) and   
   ( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND    
   ( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND    
   ( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND  
   ( a_stmt.amt_du_ic < 0 )  

Is my approach correct, or do I need to add a WHERE condition to it? I don't have access to the production database to check. The development database is in SQL Server 2012 too, sO I will not be able to run the old version there to check. It would be really kind, if someone could help me out with this!

Answer 1

I just quickly grabbed your query and re-wrote it without looking at how you rewrote it.

SELECT  DISTINCT  
   a_stmt.cyc_dte,   
   a_carr.carr_nm,   
   a_stmt.amt_du_ic  
 FROM  a_stmt 
    LEFT OUTER JOIN a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id   
    INNER JOIN a_carr on a_carr.acna = a_stmt.acna
    INNER JOIN s_dataccs on s_dataccs.acna = a_stmt.acna
WHERE (a_icc.usg_ind = 'S') and
(s_dataccs.user_id = @user_id)  and   
( ( a_stmt.acna = @acna ) or ( @acna = '' ) ) AND    
( ( a_stmt.juris_id = @juris_id ) or ( @juris_id = '' ) ) AND    
( a_stmt.jrnl_mo_yr = @jrnl_mo_yr ) AND  
( a_stmt.amt_du_ic < 0 ) ;

It seems like you have the same thing as me (or at least basically), so I would say you are good. Obviously if you could test that would be best but I feel comfortable that it is the same thing logically.