Search code examples
sqlexcelodbc

Excel ODBC SQL multiple LEFT JOIN causes DataSource.Error: ODBC: ERROR [42000]

I try to create Data ODBC connection on MS Excel (on Windows) to download data from SAGE Accountancy software. I try to include SQL statement to speed up data merging into one final report. But the code below causes the error: "DataSource.Error: ODBC: ERROR [42000] Syntax error: Invalid outer join specification"

Funny, but if I use only one LEFT JOIN statement from the code below, - data comes in (code works), however, if I use any 2 or more LEFT JOIN statements from the list below, - the same error pops out: "Invalid outer join specification".

SELECT
p.PROJECT_TRAN_ID  as TRAN_NUMBER, p.TYPE, p.ACCOUNT_REF, p.NOMINAL_CODE,p.REFERENCE as Inv_ref, p.EXTRA_REF as Extra_Inv_ref, p.DETAILS, p.QUANTITY, p.RATE, p.QUANTITY *p.RATE as NET_AMOUNT, p.TAX_AMOUNT, p.TAX_CODE, p.DEPT_NUMBER, p.DEPT_NAME, '' as USER_NAME, '' as PAID_FLAG, '' as DELETED_FLAG, '' as GROSS_AMOUNT, '' as AMOUNT_PAID, '' as OUTSTANDING, 
p1.DATE,
p2.REFERENCE as Proj_Ref, p2.NAME as Proj_name, p2.CUSTOMER_REF, p2.STATUS_ID as Proj_Status_ID, p2.START_DATE as Proj_Start, 
p3.REFERENCE as Cost_Code_ref, p3.DESCRIPTION as Cost_Code, p3.COST_TYPE_ID, 
p4.NAME as Cost_Type_Desc, 
p5.NAME as Supplier_Name,
p6.NAME as Nominal,
p7.NAME as Client

FROM PROJECT_ONLY_TRAN p
LEFT JOIN PROJECT_TRAN p1 ON p.PROJECT_TRAN_ID = p1.PROJECT_TRAN_ID
LEFT JOIN PROJECT p2 ON p1.PROJECT_ID = p2.PROJECT_ID 
LEFT JOIN PROJECT_COST_CODE p3 ON p1.COST_CODE_ID = p3.COST_CODE_ID 
LEFT JOIN PROJECT_COST_TYPE  p4 ON p3.COST_TYPE_ID = p4.COST_TYPE_ID 
LEFT JOIN PURCHASE_LEDGER p5 ON p.ACCOUNT_REF = p5.ACCOUNT_REF 
LEFT JOIN NOMINAL_LEDGER p6 ON p.NOMINAL_CODE = p6.ACCOUNT_REF
LEFT JOIN SALES_LEDGER p7 ON p2.CUSTOMER_REF = p7.ACCOUNT_REF

I have checked, - ALL tables and Columns are present.

I am familiar with SQL basics only, tried the different syntax variations, also tried LEFT OUTER JOIN without any success...

I am using SageLine50v29 ODBC data source to connect. If I create SQL server to mirror SAGE server, - multiple SQL JOIN commands do work while querying the mirror, but fails if I do it directly as mentioned above...

Solution

  • Adding the comment as an answer. It was a lucky guess, but wrapping your joins seems to do the trick. It would seem this is required for more than one join for Access-like SQL.

    LEFT JOIN PROJECT_TRAN p1 ON (p.PROJECT_TRAN_ID = p1.PROJECT_TRAN_ID)