Re: Multiple joins in Oracle

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 May 2001 14:49:49 -0400
Message-ID: <sv65htg93emdptouq50sgi38deb0651in1@4ax.com>

A copy of this was sent to gkont_at_emphasis-systems.gr (Geo) (if that email address didn't require changing) On 28 May 2001 07:53:58 -0700, you wrote:

>I am trying to migrate from MSSql server to Oracle database
>and i have problem with this kind of sql statements (because Oracle does not
>allow multiple joins). Please help!
>
>
> SELECT lg.lg_account,
> lg.lg_title,
> ca.ca_period_id,
> ca.ca_title,
> sum(dv.dv_debit) dv_debit,
> sum(dv.dv_credit) dv_credit
> FROM lg, ca, dv
> WHERE ( dv.dv_company =* lg.lg_company) and
> ( dv.dv_ypok =* lg.lg_ypok) and
> ( dv.dv_fyear =* lg.lg_fyear) and
> ( dv.dv_chart_account =* lg.lg_chart_account) and
> ( ca.ca_company *= dv.dv_company) and
> ( ca.ca_ypok *= dv.dv_ypok) and
> ( ca.ca_fyear *= dv.dv_fyear) and
> ( ca.ca_period_id *= dv.dv_ins_period) and
> ( dv.dv_account =* lg.lg_account) and
> ( ca.ca_company = lg.lg_company ) and
> ( ca.ca_ypok = lg.lg_ypok ) and
> ( ca.ca_fyear = lg.lg_fyear ) and
> ( dv.dv_is_budget = 'N') and
> ( dv.dv_is_cashflow = 'N') and
> ( dv.dv_is_forcast = 'N' )
> GROUP BY lg.lg_account, lg.lg_title, ca.ca_period_id,
> ca.ca_title
> ORDER BY lg.lg_account, lg.lg_title, ca.ca_period_id,
> ca.ca_title

it would probably be better to post the ORACLE query next time ;)

I believe their outer join is reversed from ours so

   a =* b

is

   a(+) = b

in Oracle, if so that means that you are outer joining to DV in the above query (asking for a row in DV to be "made up" if there is no matching row) but later go onto say:

> ( dv.dv_is_budget = 'N') and
> ( dv.dv_is_cashflow = 'N') and
> ( dv.dv_is_forcast = 'N' )

Now, when DV is "made up", it'll be all nulls, null won't be equal to 'N' so that row will be discarded. THEREFORE - the outer join to DV is no relevant and will only slow dow the processing of the query.

So, I believe the outer join to DV is not needed at all -- if DV.dv_is_budget = 'N' is TRUE ,then DV.dv_is_budget can NEVER be NULL -- hence the outer join is not useful here.

Just to show how this outer join conundrum could have been solved in the event it was needed, we'll do the "straight port" of the query and see the error and then rewrite it avoiding the error:

ps$tkyte_at_ORA8I.WORLD> SELECT lg.lg_account,

  2           lg.lg_title,
  3           ca.ca_period_id,
  4           ca.ca_title,
  5           sum(dv.dv_debit) dv_debit,
  6           sum(dv.dv_credit) dv_credit
  7      FROM lg, ca,  dv
  8     WHERE ( dv.dv_company (+) = lg.lg_company) and
  9           ( dv.dv_ypok (+) =  lg.lg_ypok) and
 10           ( dv.dv_fyear (+) =  lg.lg_fyear) and
 11           ( dv.dv_chart_account (+) =  lg.lg_chart_account) and
 12           ( ca.ca_company = dv.dv_company(+) ) and
 13           ( ca.ca_ypok = dv.dv_ypok(+) ) and
 14           ( ca.ca_fyear = dv.dv_fyear(+) ) and
 15           ( ca.ca_period_id = dv.dv_ins_period(+) ) and
 16           ( dv.dv_account (+) =  lg.lg_account) and
 17           ( ca.ca_company = lg.lg_company ) and
 18           ( ca.ca_ypok = lg.lg_ypok ) and
 19           ( ca.ca_fyear = lg.lg_fyear ) and
 20           ( dv.dv_is_budget = 'N') and
 21           ( dv.dv_is_cashflow = 'N') and
 22           ( dv.dv_is_forcast = 'N' )
 23   GROUP BY lg.lg_account,         lg.lg_title,     ca.ca_period_id,
 24            ca.ca_title
 25   ORDER BY lg.lg_account,            lg.lg_title,     ca.ca_period_id,
 26            ca.ca_title
 27  /
         ( ca.ca_period_id = dv.dv_ins_period(+) ) and
                           *

ERROR at line 15:
ORA-01417: a table may be outer joined to at most one other table

ops$tkyte_at_ORA8I.WORLD> SELECT lg_account,

  2           lg_title,
  3           ca_period_id,
  4           ca_title,
  5           sum(dv.dv_debit) dv_debit,
  6           sum(dv.dv_credit) dv_credit
  7      FROM ( select *                 <<<<<===== outer join this VIEW to DV
  8               from lg, ca
  9              where ( ca.ca_company = lg.lg_company ) and
 10                    ( ca.ca_ypok = lg.lg_ypok ) and
 11                    ( ca.ca_fyear = lg.lg_fyear )  ) lg_ca,  dv          
 12     WHERE ( dv.dv_company (+) = lg_ca.lg_company) and
 13           ( dv.dv_ypok (+) =  lg_ca.lg_ypok) and
 14           ( dv.dv_fyear (+) =  lg_ca.lg_fyear) and
 15           ( dv.dv_chart_account (+) =  lg_ca.lg_chart_account) and
 16           ( lg_ca.ca_company = dv.dv_company(+) ) and
 17           ( lg_ca.ca_ypok = dv.dv_ypok(+) ) and
 18           ( lg_ca.ca_fyear = dv.dv_fyear(+) ) and
 19           ( lg_ca.ca_period_id = dv.dv_ins_period(+) ) and
 20           ( dv.dv_account (+) =  lg_ca.lg_account) and
 21           ( dv.dv_is_budget = 'N') and
 22           ( dv.dv_is_cashflow = 'N') and
 23           ( dv.dv_is_forcast = 'N' )
 24   GROUP BY lg_ca.lg_account,         lg_ca.lg_title,     lg_ca.ca_period_id,
 25            lg_ca.ca_title
 26   ORDER BY lg_ca.lg_account,            lg_ca.lg_title,
lg_ca.ca_period_id,
 27            lg_ca.ca_title

 28 /

no rows selected   

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon May 28 2001 - 13:49:49 CDT