oracle -left outer join ANSI query

Question

Hi All, I am executing the below query with Oracle ANSI left outer join syntax,It's returning 107 records:

SELECT  bs_accts.acct_num,  
          bs_accts.acct_name,  
          br_data.record_id ,  
       as_users.username,   
       br_fx.fx_rate ,  
FROM    bs_accts
CROSS JOIN as_users  
CROSS JOIN br_fx
left outer join br_data 
ON (as_users.userid = br_data.last_note_user AND br_fx.iso_src = br_data.user_ten)
WHERE   br_data.state=3       
AND (br_data.acct_id  = bs_accts.acct_id) 
AND (bs_accts.acct_currency='SHS')        
AND     substr(bs_accts.acct_num,1,1)='T'     
AND br_fx.iso_dst = 'USD';

when I am executing the same query without ANSI syntax It's returning 875 records.I want 875 records with ANSI code.Below is the older left outer join syntax:

SELECT  bs_accts.acct_num,  
          bs_accts.acct_name,  
          br_data.record_id ,  
       as_users.username,   
       br_fx.fx_rate ,  
FROM    bs_accts,as_users,br_fx,br_data
WHERE   br_data.state=3       
AND (br_data.acct_id  = bs_accts.acct_id) 
AND (bs_accts.acct_currency='SHS')        
AND     substr(bs_accts.acct_num,1,1)='T'
AND     br_data.last_note_user=as_users.userid(+)  
AND br_data.user_ten = br_fx.iso_src(+) 
AND br_fx.iso_dst = 'USD';

Is there something wrong with ANSI Code above??

Answer 1

You are using CROSS JOIN, not LEFT OUTER JOIN so it is definitely not the same query.

Your old-style syntax query is wrong, or at least doesn't contain the outer join to br_fx that you presumably think it does, because of the lack of a (+) symbol on this part:

AND br_fx.iso_dst = 'USD'

That condition negates the (+) used on

AND br_data.user_ten = br_fx.iso_src(+)

so maybe you meant:

AND br_fx.iso_dst(+) = 'USD'

Answer 2

It looks to me like your syntax is wrong.

AND     br_data.last_note_user=as_users.userid(+)  
AND br_data.user_ten = br_fx.iso_src(+)

...means that you keep all the rows from br_data, even if they don't have matches in the other rows. That would be like using RIGHT JOIN in the first query.

In fact, the WHERE clause removes any NULLs introduced by the LEFT JOIN, making your OUTER JOIN into an INNER join.

left outer join br_data 
ON (as_users.userid = br_data.last_note_user AND br_fx.iso_src = br_data.user_ten)
WHERE   br_data.state=3

I'd really like you to let us know what you actually want from your query.

Is it this?

SELECT  bs_accts.acct_num,  
          bs_accts.acct_name,  
          br_data.record_id ,  
       as_users.username,   
       br_fx.fx_rate
FROM  
   br_data
   join
   bs_accts 
      on (br_data.acct_id  = bs_accts.acct_id) 
      AND (bs_accts.acct_currency='SHS')        
      AND substr(bs_accts.acct_num,1,1)='T'
      AND br_data.state=3       
   LEFT JOIN
   as_users
      ON br_data.last_note_user=as_users.userid
   LEFT JOIN
   br_fx
      ON br_data.user_ten = br_fx.iso_src
      AND br_fx.iso_dst = 'USD';

...where you want br_data and bs_accts joined (on all those different things), and then hooking into as_users and br_fx, but not eliminating anything from the join of br_data and bs_accts?

Rob