Hi all,

I'm working with Pervasive 2000i SP4 and am trying to run the
following query, however I get a syntax error. What's wrong? The
nested query ran fine by itself...

Thanks so much.


SELECT Max(OpenInvInvoiceDetail.InvDtlDueDate) AS
MaxDueDate,LeaseEquipmentAux.EquipAuxSeq,
LeaseEquipmentAux.EquipAuxCompanyNum,OpenInvInvoic eDetail.InvDtlLocCode,
LeaseDatabase.LeaseCustIDNum,LeaseCustLocation.Cus tLocCity,
LeaseCustLocation.CustLocState

FROM (SELECT OpenInvInvoiceDetail.InvDtlDueDate,LeaseEquipmentA ux.EquipAuxSeq,
LeaseEquipmentAux.EquipAuxCompanyNum,OpenInvInvoic eDetail.InvDtlLocCode,
LeaseDatabase.LeaseCustIDNum

FROM LeaseDatabase
LEFT OUTER JOIN LeaseEquipmentAux
ON LeaseDatabase.LeaseCompanyNum =
LeaseEquipmentAux.EquipAuxCompanyNum
AND LeaseDatabase.LeaseNum = LeaseEquipmentAux.EquipFormerLeaseNum

LEFT OUTER JOIN OpenInvInvoiceDetail
ON LeaseEquipmentAux.EquipAuxCompanyNum =
OpenInvInvoiceDetail.InvDtlCompanyNum
AND LeaseEquipmentAux.EquipAuxSeq =
OpenInvInvoiceDetail.InvDtlAssetKey
AND LeaseEquipmentAux.EquipFormerLeaseNum =
OpenInvInvoiceDetail.InvDtlLeaseNum

WHERE OpenInvInvoiceDetail.InvDtlDueDate <
LeaseEquipmentAux.EquipReturnDate
AND LeaseEquipmentAux.EquipAuxCompanyNum = '70')

LEFT OUTER JOIN LeaseCustLocation
ON LeaseEquipmentAux.EquipAuxCompanyNum =
LeaseCustLocation.CustLocCompanyNum
AND OpenInvInvoiceDetail.InvDtlLocCode = LeaseCustLocation.CustLocCode
AND LeaseDatabase.LeaseCustIDNum = LeaseCustLocation.CustLocIDNum

GROUP BY LeaseEquipmentAux.EquipAuxSeq,
LeaseEquipmentAux.EquipAuxCompanyNum,
OpenInvInvoiceDetail.InvDtlLocCode,
LeaseDatabase.LeaseCustIDNum, LeaseCustLocation.CustLocCity,
LeaseCustLocation.CustLocState

Sent this via Email, but try avoiding the SELECT ... FROM (SELECT ...) stuff.
Instead, create a view for the inner statement and then build the outer statement
using the view.
BB

Robin wrote: