Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Austria
    Posts
    5

    Unanswered: Help width ANSI Join Syntax

    Hello

    I have a problem on Oracle 9i writing a select with an outer join on 2 tables.

    I know this is not supported by oracles old join syntax so im trying to write the statement in Ansi-Syntax.
    The problem is I've never used the Ansi-Syntax and the Select is very heavy, so I'm asking you for help with this problem:

    Old Syntax:

    Code:
    select
      bdl.name,
      br.branche,
      count(a.anzeige_id)
    from
      bundesland bdl,
      plz p,
      betrieb_adr adr,
      betrieb b,
      branche br,
      betrieb_anzeige_rel bar,
      anzeige a
    where
      bdl.bundesland_id = p.bundesland_id(+)
      and p.plz_id = adr.plz_id(+)
      and adr.betrieb_id = b.betrieb_id(+)
      and adr.gueltig_bis is null
      and b.oenace_id(+) = br.oenace_id  <== Does not work
      and b.betrieb_id = bar.betrieb_id
      and bar.anzeige_id = a.anzeige_id
    group by bdl.name, br.branche
    order by bdl.name, br.branche
    Can anyone please tell me what the ANSI-Syntax for this Select would be?

    Thanks

    CU
    Herbert

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Good question - I have dealt with this a few times, and the multiple outer join limitation bugs me to no end.

    I think you should find this thread from OTN useful - I searched forever when I needed it, and finally found this one which helped.

    http://forums.oracle.com/forums/thre...&thread=215957

    If you are used to doing the (+) outer join method like me, you would want to use the inline view method mentioned in the thread.

    Good luck!
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Something like this should do it..
    Code:
    select t1.name,
           t1.branche,
           count( t2.anzeige_id )
      from ( select br.branche, bd1.name, adr.betrieb_id, br.oenace_id,
               from bundesland bd1, plz p, betrieb_adr adr, branche br
              where bd1.bundesland_id = p.bundesland_id(+)
                and p.plz_id = adr.plz_id(+)
                and adr.gueltig_bis is null ) t1,
           ( select b.betrieb_id, b.oenace_id
               from betried b, betriedb_anzeige_rel bar, anzeige a
              where b.betrieb_id = bar.betriedb_id
                and bar.anzeige_id = a.anzeige_id ) t2
     where t1.betrieb_id = t2.betrieb_id(+)
       and t1.oenace_id  = t2.oenace_id(+)
     group by t1.name, t1.branche
     order by t1.name, t1.branche

  4. #4
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    If my memory serves me, the "(+)" syntax is Oracle specific and not an ANSI standard. The ANSI standard uses "outer join" syntax in the from clause like this:
    Code:
    select
      bdl.name,
      br.branche,
      count(a.anzeige_id)
    from
      bundesland bdl RIGHT OUTER JOIN plz p 
        ON bdl.bundesland_id = p.bundesland_id,
      pls p RIGHT OUTER JOIN betrieb_adr adr
        ON p.plz_id = adr.plz_id,
    ...(and so on)...
    group by ...
    I don't normally use this syntax, so I may have the RIGHT vs LEFT join backwards. Check out the Oracle SQL Reference, they have examples.

    Also, I believe inline views are not ANSI syntax. Where you have a select statement in the from clause as in JMartinez' example.
    Last edited by skempins; 11-19-04 at 13:09. Reason: added code vbTag

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •