asked on
Converting old SQL Server outer join syntax
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!
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!
Microsoft SQL Server
Last Comment
kanagu ramasamy
ASKER
Hi Qlemo! First of all thanks for your detailed explanation! I was down with cold and fever for the past two days so I was not able to respond :(
I have completed most of my procedures having two tables..thanks for your explanation .. But in your explanation you have not mentioned the procedure for multiple interconnected tables.
which I have converted as
Thanks in advance!
I have completed most of my procedures having two tables..thanks for your explanation .. But in your explanation you have not mentioned the procedure for multiple interconnected tables.
SELECT a_trueup.jrnl_mo_yr,
SUM(a_mthly.tot_acct_rcv_inter) as tot_acct_rcv_inter,
SUM(a_mthly.tot_acct_rcv_intra) as tot_acct_rcv_intra,
FROM a_mthly,
a_trueup,
a_bac,
a_bdfctr,
#tmp_purch_mo_yr
WHERE ( a_trueup.acna *= a_bac.acna) and
( a_trueup.ba_cntct_id *= a_bac.ba_cntct_id) and
( a_trueup.acna = a_bdfctr.acna ) and
( a_trueup.juris_id = a_bdfctr.juris_id ) and
( a_mthly.acna = a_trueup.acna ) and
( a_mthly.juris_id = a_trueup.juris_id ) and
( a_mthly.jrnl_mo_yr = #tmp_purch_mo_yr.purch_mo_yr ) and
( a_mthly.intrm_ind = 'EOM' ) AND
( a_mthly.ip_typ <= '2' )and
( a_trueup.rlzd_mo_yr = a_bdfctr.jrnl_mo_yr ) and
( ( a_trueup.acna = @acna ) AND
( a_trueup.juris_id = @juris_id ) AND
( a_trueup.jrnl_mo_yr = @jrnl_mo_yr )
)
which I have converted as
SELECT a_trueup.jrnl_mo_yr,
SUM(a_mthly.tot_acct_rcv_inter) as tot_acct_rcv_inter,
SUM(a_mthly.tot_acct_rcv_intra) as tot_acct_rcv_intra,
FROM a_mthly join a_trueup on a_mthly.juris_id = a_trueup.juris_id,
join a_bdfctr on a_trueup.juris_id = a_bdfctr.juris_id and a_trueup.acna = a_bdfctr.acna and a_trueup.rlzd_mo_yr = a_bdfctr.jrnl_mo_yr
a_bac,
a_bdfctr,
#tmp_purch_mo_yr
WHERE ( a_trueup.acna *= a_bac.acna) and
( a_trueup.ba_cntct_id *= a_bac.ba_cntct_id) and
( a_mthly.acna = a_trueup.acna ) and
( a_mthly.juris_id = a_trueup.juris_id ) and
( a_mthly.jrnl_mo_yr = #tmp_purch_mo_yr.purch_mo_yr ) and
( a_mthly.intrm_ind = 'EOM' ) AND
( a_mthly.ip_typ <= '2' )and
( ( a_trueup.acna = @acna ) AND
( a_trueup.juris_id = @juris_id ) AND
( a_trueup.jrnl_mo_yr = @jrnl_mo_yr )
)
Thanks in advance!
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks a lot Qlemo ! that was really helpful!
Further you should write
Open in new window
asOpen in new window
for obvious reasons.Your first transformation is not correct. You are using the result of the "nullable" table in a restriction ( a_icc.usg_ind = 'S' ) outside of the outer join condition, and that makes it an inner join. You should use the following code instead; and it is also a good idea to apply the outer join last to keep away from more issues:
Open in new window
(you can leave out INNER and OUTER as you can see, and I prefer to do so for both).On another note, right outer joins are difficult to read. It is much easier to think about a query using left joins, just more natural. But the second query is correct, it needs to have the restrictions in the ON clause because all restrictions are against the "nullable" table, But: Is it really correct you do not apply any restriction against the outer table? This leads to getting the complete r_rj table content, and just joined additional data to it if found and appliable.