We help IT Professionals succeed at work.
Get Started

Converting old SQL Server outer join syntax

83 Views
Last Modified: 2015-09-01
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!
Comment
Watch Question
"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE