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!
kanagu ramasamyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Generally you should move anything not directly related to joins into a WHERE clause. For inner joins it does not matter, but outer joins can change their meaning.

Further you should write
(( 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 ) )

Open in new window

as
SUBSTRING (a_ovrudr.jrnl_mo_yr, 3,4) in (@yr_1, @yr_2, @yr_3 , @yr_4, @yr_5 )

Open 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:
SELECT  DISTINCT  
   a_stmt.cyc_dte,   
   a_carr.carr_nm,   
   a_stmt.amt_du_ic  
from a_stmt
join  a_carr on a_stmt.acna = a_carr.acna
join  s_dataccs on a_stmt.acna = s_dataccs.acna
left join a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id  and  a_icc.usg_ind = 'S'
where
   ( 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 )

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.
kanagu ramasamyAuthor Commented:
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.

 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 )    
      )

Open in new window


 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 )    
      )

Open in new window

Here Im not sure how to connect the tables a_trueup and a_bac  as I have already connected them through joins.. that is the sequencing of joins is very difficult.. It would be really so kind of you if you could convert this so that I can do the same for the remaining ..


Thanks in advance!
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You just continue with each join one by one, as you have started with already. You need to care about the sequence - ANSI Joins are processed from left to right, so all data to join on has to be available further on the left. In general:
tbl1 join tbl2 on tbl1.a = tbl2.a
join tbl3 on tbl3.a = tbl1.a
join ...

Open in new window

(note, no comma after the ON clause!).
Outer joins are more difficult, and I understand you having trouble with that ;-). It helps to first consider those, by locating the =* and *= tables, and transforming in pseudocode, so:
a_trueup *= a_bac   (acna, ba_cntct_id)

Open in new window

As said before, we should put the outer join as late as possible, so I'll move it after all other joins. All tables but a_bac will be joined first.
It also makes sense to designate one table as the "master", and I would use a_trueup for that, because most of WHERE and join conditions are related to that table. So we start with that table, and join everything to it.
On another note, i usually use one-letter aliases for tables, but it is arguable whether that makes the query more readable. Here I'm only using "t" for the temp table.
It also helps to always use the join fields in the same sequence as the tables have been introduced.
 
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_trueup 
       join a_mthly            on a_trueup.acna =  a_mthly.acna and a_trueup.juris_id =  a_mthly.juris_id
       join a_bdfctr           on a_trueup.acna = a_bdfctr.acna and a_trueup.juris_id = a_bdfctr.juris_id and a_trueup.rlzd_mo_yr = a_bdfctr.jrnl_mo_yr
       join #tmp_purch_mo_yr t on a_mthly.jrnl_mo_yr = #tmp_purch_mo_yr.purch_mo_yr
  left join a_bac              on a_trueup.acna =    a_bac.acna and a_trueup.ba_cntct_id = a_bac.ba_cntct_id
  WHERE
        a_trueup.acna     = @acna
    and a_trueup.juris_id = @juris_id 
    and a_trueup.jrnl_mo_yr = @jrnl_mo_yr
    and a_mthly.intrm_ind = 'EOM'
    and a_mthly.ip_typ   <= '2'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kanagu ramasamyAuthor Commented:
Thanks a lot Qlemo ! that was really helpful!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.