The following SQL (V3.30) runs in DBSys but generates a "DBISAMEgnineError: 11949 'Table
CP is the target of multiple join conditions in WHERE or JOIN close" error when run within
my Delphi App.

I am mystified as to why it works in DBSys ... running in less than a second, but doesn't
run in my app ... any suggestions?

Here is the SQL:


SELECT
 SP.ID,
 SP.CustOrderRef,
 SP.TraceCode,
 SP.DateWanted,
 SP.TotalQtyOrdered,
 SP.TotalNet,
 SP.SupplierComment,
 P.Name AS Product,
 P.ProdCode,
 SPI.QtyOrdered,
 SPI.QtyReceived,
 SPI.Weight,
 SPI.TValue,
 SPI.VAT,
 CS.Name AS Customer,
 CS.MainContact,
 CS.FaxNum,
 CS.PhoneNum,
 CS.AddressMemo,
 S.FirstName + " " + S.LastName as StaffName,
 T.Name AS DeliveryMethod
FROM
 StockPurchase SP
 LEFT JOIN StockPurchaseItem SPI ON (SP.ID = SPI.StockPurchaseID)
 LEFT JOIN Product P ON (SPI.ProductID = P.ID)
 LEFT JOIN CustomerProduct CP ON (P.ID = CP.ProductID) AND (SP.CustID = CP.CustID) /* <==
causing problem??*/
 LEFT JOIN CustomerSupplier CS ON (SP.CustID = CS.ID)
 LEFT JOIN Type T ON (SP.DeliveryMethodID = T.ID)
 LEFT JOIN Staff S ON (S.ID = SP.EmpIDOrdered)

WHERE SP.ID = 13586

Adam,

<< The following SQL (V3.30) runs in DBSys but generates a
"DBISAMEgnineError: 11949 'Table CP is the target of multiple join
conditions in WHERE or JOIN close" error when run within my Delphi App.

I am mystified as to why it works in DBSys ... running in less than a
second, but doesn't run in my app ... any suggestions? >>

The only way that this is possible is if you're compiling your application
with a different version of DBISAM.  There's no other way if you're using
the same exact SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com

The only way that this is possible is if you're compiling your application
with a different version of DBISAM.  There's no other way if you're using
the same exact SQL.

--

Thank you for this Tim, my DBISAM versions are the same ... but when I run DBSys I am
working with non-Client-Server, whereas my App is Client-Server.

Might this cause a problem?

--

Also: Just to check, it is OK to make a table the target of multiple joins isn't it? I
don't usually have to do this as my DB design normally doesn't require it, but in this
case the problem is unavoidable.

Adam

Adam,

<< Thank you for this Tim, my DBISAM versions are the same ... but when I
run DBSys I am working with non-Client-Server, whereas my App is
Client-Server.

Might this cause a problem? >>

Sure.  More than likely the database server that you're accessing was
compiled using an older version of DBISAM.  Make sure that you're running
the same version of DBISAM on the database server.

<< Also: Just to check, it is OK to make a table the target of multiple
joins isn't it? I don't usually have to do this as my DB design normally
doesn't require it, but in this case the problem is unavoidable. >>

With 3.x it was a little dicey at times, especially with earlier 3.x
versions.  3.30 should be able to handle all join types, as well as 4.x and
above.

--
Tim Young
Elevate Software
www.elevatesoft.com