Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Location
    UK
    Posts
    12

    Angry Unanswered: Oracle - Complex sub query - urgent help

    I have 5 tables involved in a query as follows:
    Sales Order Header(TABLE G13) joined to multiple Sales Order lines(TABLE G14).
    Each sales order line (TABLE G14) linked to a sales order/production order cross reference table (TABLE G34) and also a specification view (OTY).
    The cross link table (TABLE G34) is also linked to a production order table (TABLE F01).

    What I need to generate is the following ...

    Retrieve all sales orders (G13/G14/OTY) and all linked production orders (G34/F01)

    example

    S/O No ...... P/O No ...
    SO12345 PO12345
    SO12345 PO12346
    SO12345 PO12347
    SO12346 PO12348

    etc.

    I know it is an outer join to get all Sales Orders regardless of whether there are any linked Production Orders but I have been told that this is only possible on a maximum of 2 tables?
    I tried using the Oracle (+) syntax but got an error.

    I then tried putting a nested SELECT in the FROM clause also with problems.

    I know this is a very complex task and I am a beginner.

    I also need to do this without creating a view if possibel.

    Any advice appreciated.

    I have attached my current SQL whcih as you will see is complex and broken!
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle - Complex sub query - urgent help

    I cannot follow your SQL (who on Earth came up with those ridiculous table names? A sadist, presumably!), but can shed some light on the issue of outer joins. To say outer joins are "only possible on a maximum of 2 tables" is misleading. What it refers to is that you can't do this:

    WHERE A.b (+) = B.b
    AND A.c (+) = C.c

    i.e. the "outer" table A can only be outer joined to 1 "inner" table (B or C, not both).

    It IS perfectly legitimate to do this:

    WHERE A.b (+) = B.b
    AND C.b (+) = B.b

    i.e. 2 (or more) "outer" tables (A and C) joined to 1 "inner" table B.

    And it IS legitimate to do this:

    WHERE A.B (+) = B.b
    AND B.c (+) = C.c

    i.e. a chain of "outer" tables (A,B) linked to an "inner" table (C).

    So in a nutshell the rule is that each outer joined table can be joined to only 1 "inner" table.

    From your description above, it sounds like you want to do this (in essence):

    WHERE G13 = G14
    AND G14 = OTY
    AND G14 = G34 (+)
    AND G34 = F01 (+)

    ... which is perfectly legal.

    I hope that helps.

  3. #3
    Join Date
    Apr 2002
    Location
    UK
    Posts
    12

    Thanks!

    I'll give it a try!
    Would I be better just using the INNER/OUTER join syntax rather than the sub query?

    I just got a scaled down version working in Access ...

    SELECT SO_HEADER.CUSTOMER, SO_HEADER.ORDERNO, SO_LINE.SO_HEADER, SO_LINE.QTY, WO_SO_LINK.WORDNO, WO.ITEM, SPEC.NAME, WO_SO_LINK.QTY
    FROM SPEC RIGHT JOIN ((SO_HEADER INNER JOIN (SO_LINE LEFT JOIN WO_SO_LINK ON SO_LINE.SO_HEADER = WO_SO_LINK.SORDNOWOR) ON SO_HEADER.SORDNO = SO_LINE.SO_HEADER) LEFT JOIN WO ON WO_SO_LINK.WORDNO = WO.[WO NO]) ON SPEC.ITEM = WO.ITEM
    WHERE (((SO_HEADER.CUSTOMER)="Cust"));

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Would I be better just using the INNER/OUTER join syntax?"

    YES!!

    rudy
    http://rudy.ca/

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Thanks!

    Originally posted by ARC
    Would I be better just using the INNER/OUTER join syntax rather than the sub query?
    Well, it sounds like the outer join does what you want. I'm not sure what your subquery version does, but I would not try use a subquery to replace an outer join - in fact, I have no idea how you could, they are completely different concepts.

Posting Permissions

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