When using Jet with a NL(Sum) on a SQL datasource (Exact Online) through a CData ODBC driver, the result is a statement with a lot of ‘OR’ clauses. This causes a recursion error.

We asked CData on how to bypass this, and here is what they suggest:

The dev team has determined a fix for this issue.

This is a Server thrown issue due to the very large number of OR clauses, overriding the recursion limit. Luckily, using an IN clause is a valid workaround, for example you can convert your existing query to:
SELECT SUM("AmountDC") FROM "CData"."ExactOnline"."GeneralJournalEntryLines" WHERE "Division" = 71655 AND "GLAccountCode" IN ('8100', '8101', '8102', '8110', '8111', '8150', '8151', '8152', '8160', '8161', '8190', '8191', '8192', '8193', '8194', '8200', '8205', '8210', '8211', '8215', '8216') AND "Date" BETWEEN {ts '2024-01-01 00:00:00'} AND {ts '2024-01-31 00:00:00'} which will bypass the issue.

This is the current SQL statement when using NL(Sum). Would it be possible to achieve this result in a Jet formula, for example when using NL("Rows","SQL=Select * from…with the IN clause as suggested by CData?

Query Failed: Error: Recursion reached allowed limit.
In the ODBC driver I enabled the logging and found below error in the log file. Is there a setting on the odbc driver to increase the recursion limit?

2024-06-11T14:53:16.030+02:00 1 [22|Q-Id] [SQL |Messag] Query Failed: [SELECT SUM("AmountDC") FROM "CData"."ExactOnline"."GeneralJournalEntryLines" WHERE ("Division" = 722266) AND ("GLAccountCode" = '8100' OR "GLAccountCode" = '8101' OR "GLAccountCode" = '8102' OR "GLAccountCode" = '8110' OR "GLAccountCode" = '8111' OR "GLAccountCode" = '8150' OR "GLAccountCode" = '8151' OR "GLAccountCode" = '8152' OR "GLAccountCode" = '8160' OR "GLAccountCode" = '8161' OR "GLAccountCode" = '8190' OR "GLAccountCode" = '8191' OR "GLAccountCode" = '8192' OR "GLAccountCode" = '8193' OR "GLAccountCode" = '8194' OR "GLAccountCode" = '8200' OR "GLAccountCode" = '8205' OR "GLAccountCode" = '8210' OR "GLAccountCode" = '8211' OR "GLAccountCode" = '8215' OR "GLAccountCode" = '8216') AND ("Date" BETWEEN {ts '2024-01-01 00:00:00'} AND {ts '2024-01-31 00:00:00'})]. Error: Recursion reached allowed limit