Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Wednesday 13 November 2013

Sage Line 50 ODBC driver outer joins

I've just discovered that you can do outer joins with the Sage Line 50 ODBC drivers. Happy days!

If you try and implement an outer join in MS Query it will use a syntax like:

FROM {oj AUDIT_JOURNAL AUDIT_JOURNAL LEFT OUTER JOIN SALES_LEDGER SALES_LEDGER ON AUDIT_JOURNAL.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF}

Unfortunately this behaves like an inner join!  To get proper outer join functionality the easiest way I've found is to create an inner join and then, in the SQL that is created, add '(+)' to the end of the join criteria line as shown below:

FROM AUDIT_JOURNAL AUDIT_JOURNAL, SALES_LEDGER SALES_LEDGER 
WHERE AUDIT_JOURNAL.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF(+) 

I always understood the (+) notation (used with Oracle databases) as "ALL values from 'AUDIT_JOURNAL' and all matching records from 'SALES_LEDGER' where AUDIT_JOURNAL.ACCOUNT_REF=SALES_LEDGER.ACCOUNT_REF PLUS NULL records where there is no match for AUDIT_JOURNAL.ACCOUNT_REF".

I'm learning every day!

P.S. If you use MS Access to link to Sage tables I've found that you can't identify any key fields and to get the above syntax to work the query has to be a pass-through query. If anyone finds differently perhaps you'd care to share via a comment? 

No comments:

Post a Comment