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!

• Moved by Lydia ZhangMicrosoft contingent staff, Moderator 5 hours 14 minutes ago more appropriate

Hi Kanagu,

The old fashioned outer join is ambiguous and hard to tell how it is predicated. Using the new join with a explicit outer join operator is much more clear. However a condition would return different result, depending on the condition is after ON or WHERE. Please see below demonstration.

CREATE TABLE T1(id INT);
INSERT INTO T1 VALUES(1),(2);

CREATE TABLE T2(id INT);
INSERT INTO T2 VALUES(2),(3); 
 
 SELECT * FROM t1 LEFT JOIN t2 ON t1.id =t2.id AND T1.ID=2
 SELECT * FROM t1 LEFT JOIN t2 ON t1.id =t2.id WHERE T1.ID=2

The reason is JOIN conditions evaluates first and then WHERE clause of the SQL statement. See Why LEFT JOIN doesn't bring all records from the LEFT table?

So in your case, when converting the query, due to the old fashioned out query's predicating ambiguities, pay attention to where to place the conditions according to your business logic.

If you have any question, feel free to let me know.

*= is left outer join

=* is right outer join

Here is an example:

SELECT Customers.CustomerId

FROM customers, orders
WHERE customers.Customerid *= orders.CustomerId
   AND orders.CustomerId IS NULL
-------------------------------------------------
SELECT Customers.CustomerId  
FROM customers LEFT OUTER JOIN orders 
ON customers.CustomerId = orders.CustomerId 
WHERE orders.CustomerId IS NULL