converting old sql outer join syntax =*/*=
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 )  


                            Here is one more :
                            SELECT r_rj.juris_id, "GRAND TOTAL", "",  
                            round( isnull (sum(a_ovrudr.bd_intl + a_ovrudr.bd_inter + a_ovrudr.bd_intra+  
                            a_ovrudr.bd_fed_tx + a_ovrudr.bd_st_lcl_tx), 0), 2),    
                            r_rj.ord    
                            FROM a_ovrudr,            r_rj    
WHERE ( a_ovrudr.juris_id =* r_rj.juris_id) and  
                            ( a_ovrudr.acna = @acna  ) AND    
                            (( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_1 ) OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_2 )  OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_3 ) OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_4 ) OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_5 ) )    
                            GROUP BY   a_ovrudr.acna,  r_rj.juris_id, r_rj.ord  
                            ORDER BY  yr, mo, r_rj.ord

                            which I have converted as :
                            SELECT r_rj.juris_id, "GRAND TOTAL", "",  
                            round( isnull (sum(a_ovrudr.bd_intl + a_ovrudr.bd_inter + a_ovrudr.bd_intra+  
                            a_ovrudr.bd_fed_tx + a_ovrudr.bd_st_lcl_tx), 0), 2),    
                            r_rj.ord    
FROM a_ovrudr right outer join r_rj on a_ovrudr.juris_id = r_rj.juris_id and  
                            ( a_ovrudr.acna = @acna  ) AND    
                            (( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_1 ) OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_2 )  OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_3 ) OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_4 ) OR  
                            ( SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) = @yr_5 ) )    
                            GROUP BY   a_ovrudr.acna,  r_rj.juris_id, r_rj.ord  
                            ORDER BY  yr, mo, r_rj.ord 



                            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!  Thanks!
August 22nd, 2015 9:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics