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

From: orekin <nicholasrolfe_at_yahoo.com.au>
Date: 12 Jun 2004 08:17:13 -0700
Message-ID: <6f87d1d0.0406120717.259c651f_at_posting.google.com>

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 Received on Sat Jun 12 2004 - 17:17:13 CEST