Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Exclamation Unanswered: Please Help!! I'm going to JOIN a mental asylum!!

    I've tried this query with a few DBMS, but they either freeze or tell me Ambiguous Outer Join:

    SELECT "OEINVD"."ITEM" as "Item #", "OEINVD"."QTYSHIPPED" as "Quantity Shipped", "OEINVD"."EXTINVMISC" as "$ Sales", "OEINVD"."EXTICOST" as "$COGS", "OEINVH"."INVNUMBER" as "Invoice #", "OEINVH"."CUSTOMER" as "Customer", "OECRDH"."CRDNUMBER" as "Credit Note #", SUM("OECRDD"."QTYRETURN") as "Quantity Returned",SUM("OECRDD"."EXTCRDMISC") as "$ Return", SUM("OECRDD"."EXTCCOST") as "$ Return COGS"

    FROM (("OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNUMBER" = "OEINVH"."INVNUMBER") LEFT OUTER JOIN "OECRDH" ON "OEINVH"."INVNUMBER" = "OECRDH"."INVNUMBER") INNER JOIN "OECRDD" ON "OECRDH"."CRDUNIQ" = "OECRDD"."CRDUNIQ"

    WHERE "OEINVD"."QTYSHIPPED">0 AND "OEINVH"."INVDATE">20040501 AND "OEINVD.ITEM" = "OECRDD.ITEM"

    GROUP BY "OEINVD"."ITEM", "OEINVD"."QTYSHIPPED", "OEINVD"."EXTINVMISC", "OEINVD"."EXTICOST","OEINVH"."INVNUMBER","OEINVH". "CUSTOMER", "OEINVH"."INVDATE", "OECRDH"."CRDNUMBER";

    ....

    OEINVH & OEINVD represent invoices, and are linked to each other via inner join on INVNUMBER

    OECRDH and OECRDD represent credit notes and are linked to each other via inner join on CRDUNIQ

    What I'm trying to do is list all the invoice detail lines (OEINVD) with credit note information if applicable. My first problem is that the detail level information for credit notes is in OECRDD. The only way I can see to link from OEINVD is:

    OEINVD.INVNUMER = OEINVH.INVNUMBER

    OEINVH.INVNUMBER OuterLeft OECRDH.INVNUMBER

    OECRDH.CRDUNIQ = OECRDD.CRDUNIQ

    OEINVD.ITEM = OECRDD.ITEM

    I need that last join, otherwise the recordset would have incorrect summing on the credit note side.

    My second issue is this - not every invoice has a credit note, and just to make life difficult, there can be > 1 credit note against an invoice! So in other words an invoice could have 0,1 or more credit notes against it. Where there is > 1 credit note against an invoice I want to sum the credit note fields.

    I've come to a dead end, how can I get this working ?
    Thanks
    Bill

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks like microsoft access

    whaddya mean, "tried this query with a few DBMS"????

    where do you want it to run?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    6

    Joins

    I want to run it in Access. I have actually got it working in Pervasive, but not in Access, which won't accept the 'Ambiguous Outer Joins' ? For someone that knows SQL this would probably be easy to figure out ....

    Here is a simple access database with the four tables I am interested
    in:

    http://s2.yousendit.com/d.aspx?id=8D...CDD280DECE067B

    Here is a csv file showing my desired output:

    http://s2.yousendit.com/d.aspx?id=5E...83D4FD933F3F0C

    Neither of the above contain macros.

    Below is the query I wrote in Pervasive v8, but:
    (a) It crashes Pervasive for all but the smallest databases
    (b) I'm pretty much positive it is badly written !!!

    Thanks In Advance
    Bill
    __________

    SELECT

    "OEINVH"."INVNUMBER" as "Invoice #",
    "OEINVH"."CUSTOMER" as "Customer",
    "OEINVH"."SHIPTO" as "Ship to Code",
    "OEINVH"."SHPNAME" as "Ship To Name",
    "OEINVH"."INVNETNOTX" as "Invoice Total",
    "OEINVD"."ITEM" as "Item #",
    "OEINVD"."QTYSHIPPED" as "Quantity Shipped",
    "OEINVD"."EXTINVMISC" as "$ Sales",
    "OEINVD"."EXTICOST" as "$COGS",
    SUM("OECRDD"."QTYRETURN") as "Quantity Returned",
    SUM("OECRDD"."EXTCRDMISC") as "$ Return",
    SUM("OECRDD"."EXTCCOST") as "$ Return COGS"

    FROM "OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNUMBER" =
    "OEINVH"."INVNUMBER"
    INNER JOIN "OECRDH" ON "OEINVH"."INVNUMBER" = "OECRDH"."INVNUMBER"
    INNER JOIN "OECRDD" ON "OECRDH"."CRDUNIQ" = "OECRDD"."CRDUNIQ"

    WHERE "OEINVD"."QTYSHIPPED">0 AND "OEINVD"."ITEM" = "OECRDD"."ITEM"

    GROUP BY
    "OEINVH"."INVNUMBER",
    "OEINVH"."CUSTOMER",
    "OEINVH"."SHIPTO",
    "OEINVH"."SHPNAME",
    "OEINVH"."INVNETNOTX",
    "OEINVD"."ITEM",
    "OEINVD"."QTYSHIPPED",
    "OEINVD"."EXTINVMISC",
    "OEINVD"."EXTICOST"

    UNION

    SELECT
    "OEINVH"."INVNUMBER" as "Invoice #",
    "OEINVH"."CUSTOMER" as "Customer",
    "OEINVH"."SHIPTO" as "Ship to Code",
    "OEINVH"."SHPNAME" as "Ship To Name",
    "OEINVH"."INVNETNOTX" as "Invoice Total",
    "OEINVD"."ITEM" as "Item #",
    "OEINVD"."QTYSHIPPED" as "Quantity Shipped",
    "OEINVD"."EXTINVMISC" as "$ Sales",
    "OEINVD"."EXTICOST" as "$COGS",
    convert(0,sql_decimal) as "Quantity Returned",
    convert(0,sql_decimal) as "$ Return",
    convert(0,sql_decimal) as "$ Return COGS"

    FROM "OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNUMBER" =
    "OEINVH"."INVNUMBER"

    WHERE "OEINVD"."QTYSHIPPED">0 AND ("OEINVH"."INVNUMBER" NOT IN (SELECT
    DISTINCT OECRDH.INVNUMBER from OECRDH));

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access requires that you parenthesize your joins
    Code:
    select ...
      from (
           (
           OEINVD 
    inner 
      join OEINVH 
        on OEINVD.INVNUMBER 
         = OEINVH.INVNUMBER
           )
    inner 
      join OECRDH 
        on OEINVH.INVNUMBER 
         = OECRDH.INVNUMBER
           )
    inner 
      join OECRDD 
        on OECRDH.CRDUNIQ 
         = OECRDD.CRDUNIQ
    sorry, can't test this, can't read your MDB
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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