2

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!

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.