/*non-working sql*/
SELECT ar.TransDate, ar.TransType, ar.TransNum, invitem.stockcode
FROM ar ar
     LEFT OUTER JOIN invoice ON ar.Transnum = invoice.Number
     JOIN invitem ON invoice.Number = invitem.Number
     JOIN code ON invitem.StockCode = code.StockCode
WHERE ( ar.TransDate >= '2004-10-04' ) and (ar.transtype = 1)


/*working sql - changed last retrieved field to code.stockcode*/
SELECT ar.TransDate, ar.TransType, ar.TransNum, code.stockcode
FROM ar ar
     LEFT OUTER JOIN invoice ON ar.Transnum = invoice.Number
     JOIN invitem ON invoice.Number = invitem.Number
     JOIN code ON invitem.StockCode = code.StockCode
WHERE ( ar.TransDate >= '2004-10-04' ) and (ar.transtype = 1)

If I change the first line of the query to:

  SELECT ar.TransDate, ar.TransType, ar.TransNum, invitem.stockcode,
code.stockcode

You can see where the values of invitem.stockcode and
code.stockcode are not all the same even though they
should be given that there is an inner join between the
two tables on that one field.

---

 Comments and Workarounds

---

The actual problem is with the joins - the fact that the first LOJ generates a NULL condition that is further used with the second INNER join causes a problem with DBISAM that prevents it from properly processing the third INNER join. The workaround is to two-stage the joins using two queries in a script. You'll have to have the first query do the LOJ and then the second query take the first result and then do the two INNER joins.

---

 Resolution

---

Fixed Problem on 11/5/2004 in version 4.15 build 1