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!