Pervasive v8 accepts this query, but then freezes:

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

Does the query freeze the engine, or does the engine go off to work on it,
and just never come back, since it has so much work to do? Check the CPU
utilization and see if the database manager is using up a big chunk of it,
or if it is accessing the disk a lot.

As for your query, I'd recommend using a subquery to build to credit note
totals and including them that way. Otherwise, some records may be
filtered by the EquiJoins.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: August, 2004: See our web site for details!

orekin wrote: