Re: Translating outer joins

From: Mike Kienenberger (mkienen..mail.com)
Date: Thu Aug 17 2006 - 21:52:29 EDT

  • Next message: Mike Kienenberger: "Re: Translating outer joins"

    SELECT * FROM (SELECT [...] FROM

     ENG_WORK_MGMT.FEE t0,
     ENG_WORK_MGMT.FEE_TYPE t1,
     ENG_WORK_MGMT.FEE_CYCLE
     ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
     ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
     ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5

    WHERE
      t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
     AND t0.FEE_ID = t2.FEE_CYCLE_ID
     AND t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID(+)
     AND t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
     AND t2.FEE_CYCLE_ID = t5.RECURRING_FEE_CYCLE_ID(+)

     AND ((t1.DESCRIPTION = ?)
     AND ((t4.AGENCY_ID = ?)
     OR (t4.AGENCY_ID = ?))))

    Ok. I appear to be having some kind of unwanted optimization occurring.

    There should be a line that says

     AND t5.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)

    or maybe even

     ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
     AND t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
    with (t6.AGENCY_ID = ?)

    On 8/17/06, Mike Kienenberger <mkienen..mail.com> wrote:
    > Ok. I think I have Oracle8 style outer joins working. I'll take a
    > shot at the other ones tomorrow.
    >
    > On 8/17/06, Andrus Adamchik <andru..bjectstyle.org> wrote:
    > > Yes, actually there was some discussion before to use such syntax for
    > > the inner joins as well. I am all for it (I guess we have to preserve
    > > a backdoor for the old syntax in case some db does not support such
    > > syntax).
    > >
    > > Andrus
    > >
    > > On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
    > >
    > > > Even better link
    > > >
    > > > http://www.devx.com/dbzone/Article/17403/0/page/3
    > > >
    > > > Looks like we do away with WHERE clause joins altogether (at least for
    > > > Oracle) and explicly join everything with ON statements.
    > > >
    > > > On 8/17/06, Mike Kienenberger <mkienen..mail.com> wrote:
    > > >> This is somewhat helpful for the various kinds of joins.
    > > >>
    > > >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
    > > >>
    > > >> Still looking for complex examples.
    > > >>
    > > >> On 8/17/06, Mike Kienenberger <mkienen..mail.com> wrote:
    > > >> > On 8/17/06, Andrus Adamchik <andru..bjectstyle.org> wrote:
    > > >> > > It would be nice if we could implement the translator using
    > > >> standard
    > > >> > > SQL syntax ("left outer join" instead of "(+)"), as it will
    > > >> work on
    > > >> > > most DB's including Oracle (starting from 9i), while the "(+)"
    > > >> syntax
    > > >> > > only works on Oracle (and is probably considered legacy syntax by
    > > >> > > Oracle too).
    > > >> > >
    > > >> > > select
    > > >> > > name,
    > > >> > > department_name
    > > >> > > from
    > > >> > > employees e
    > > >> > > left outer join
    > > >> > > departments d
    > > >> > > on
    > > >> > > e.department_id = d.department_id;
    > > >> > >
    > > >> > > It will be somewhat harder to implement, but will solve the issue
    > > >> > > once and for all.
    > > >> >
    > > >> > Well, sure, now you tell me :-)
    > > >> >
    > > >> > My Oracle Reference Book is Oracle8, so I didn't realize we had a
    > > >> > better choice :-)
    > > >> >
    > > >> > I guess I need to see if I can find some documentation on this
    > > >> format.
    > > >> >
    > > >> > The simple example is obvious, but what does it look like with more
    > > >> > tables involved, some with more outer joins and some without?
    > > >> >
    > > >>
    > > >
    > >
    > >
    >

    This archive was generated by hypermail 2.0.0 : Thu Aug 17 2006 - 21:52:54 EDT