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!