I am trying to use subqueries and joins in my PHP script that is pulling data from SAGE, but I keep getting this error: Warning: odbc_exec(): SQL error: [ProvideX][ODBC Driver]Expected lexical element not found: <identifier>
Here is my query:
SELECT *
FROM (
SELECT SO_InvoiceHeader.*, SO_InvoiceDetail.ItemCode
FROM SO_InvoiceHeader,
{lj LEFT JOIN SO_InvoiceDetail
ON SO_InvoiceHeader.InvoiceNo = SO_InvoiceDetail.InvoiceNo
}
WHERE SO_InvoiceHeader.InvoiceDate
BETWEEN {d $startDate }
AND {d $endDate }
) AS invoices
{lj LEFT JOIN CI_Item
ON invoices.ItemCode = CI_Item.ItemCode
}
WHERE CI_Item.Category1 = 'SWINE'
AND CI_Item.Category4 = 'BI'
Are subqueries supported? Is my join syntax correct?
I have seen some people suggest making a number of small queries, and then querying those results... but how do you do that?
Thanks!