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!