The following statement is an incremental update, switching all registered accounts to receive both a PDF as well as XML invoice:
update /*+ http_memory_cache(false) http_disk_cache(false) */ ExactOnlineREST..Accounts@eol set InvoiceAttachmentType = 3 where InvoiceAttachmentType != 3 limit 1000
The typical runtime is less than a second, since due to the incremental behaviour only few records will be selected. The typical API consumption is one API-call to retrieve the records and one API-call for each record to be updated.
When mandatory filtering on Exact Online is activated, an error will occur such as:
A required filter is missing.
The remote server returned an error: (400) Bad Request.
or on older releases:
A GET request for this endpoint must have a $filter parameter with one of the following fields in the url:Accountant,AccountManager,AddressLine1,AddressLine2,AddressLine3,Blocked,BRIN,BSN,ChamberOfCommerce,City,Classification1,Classification2,Classification3,Classification4,Classification5,Classification6,Classification7,Classification8,Classification,Code,CodeAtSupplier,Country,Created,Email,EndDate,Fax,ID,InvoiceAccount,IsAccountant,IsReseller,IsSales,IsSupplier,MainContact,Name,Parent,Phone,Postcode,PriceList,Reseller,RSIN,SearchCode,StartDate,Status,Type,VATNumber.
The implementation of mandatory filtering leaves room for improvements. In this case, the
InvoiceAttachmentType was not included as a sufficient filter. Even worse, without mandatory filtering it could be used as a filter and could be very selective since being contained in the base table:
In a future release of Invantive SQL a workaround will be implemented. This might take the form of disabling server-side filtering altogether for columns that could previously be used for optimal performance.
For now, you can use one of the following two alternatives to cope with mandatory filtering on Exact Online.
First alternative is to offload processing to the client software using the sync APIs and combining the selection with individual update statements as previously. Using individual update statements does not introduce extra processing time since Exact Online does not support the bulk update specification of OData. The processing requirements locally will grow somewhat because the sync APIs require an update, while in the original query the SQL Server of Exact Online filtered the data.
The resulting code becomes either in PSQL:
begin for r in ( select id from AccountsIncremental@eol where invoiceattachmenttype != 3 limit 1000 ) loop update ExactOnlineREST..Accounts@eol set invoiceattachmenttype = 3 where id = r.id ; end loop; end;
Or in SQL:
update /*+ http_memory_cache(false) http_disk_cache(false) */ ExactOnlineREST..Accounts@eol set InvoiceAttachmentType = 3 where ID in ( select /*+ low_cost */ ID from AccountsIncremental@eol where InvoiceAttachmentType != 3 limit 1000 )
Note the use of the
low_cost SQL-hint. Otherwise still an
itgeneor330 will occur, since the
AccountsIncremental is not yet considered a low-cost table which enables early evaluation in query execution.
Mandatory filtering is poorly implemented to achieve it’s goal of reaching increased use of the sync APIs. Mandatory filtering can easily be tricked into accepting a filter even when it is no real filter. Mandatory filtering ignores the selectiveness of a filter value or filter operator, so filters can easily be constructed which will return close to 100% of the data can easily be derived.
Using this fact, an optimistic approach would be to use:
update /*+ http_memory_cache(false) http_disk_cache(false) */ exactonlinerest..accounts@eol set invoiceattachmenttype = 3 where invoiceattachmenttype != 3 and Created > to_date('19700101', 'YYYYMMDD')
Created is a mandatory column with a lower-bound well above 1970, this filter should work. The resulting API call is:
…/Accounts?$select=*&$filter=Created gt datetime’1970-01-01T00:00:00’ and InvoiceAttachmentType ne 3
However, mandatory filtering no longer accepts this filter due to a bug as described above. By rewriting the query to not exclude the filter on
InvoiceAttachmentType mandatory filtering from the server-side filter, the Exact Online API can be tricked into accepting the statement:
update /*+ http_memory_cache(false) http_disk_cache(false) */ exactonlinerest..accounts@eol set invoiceattachmenttype = 3 where invoiceattachmenttype + 1 != 3 + 1 and Created > to_date('19700101', 'YYYYMMDD')
The last statement has a significant downside: it generates hundreds of API calls like:
…/Accounts?$filter=Created gt datetime’2020-08-02T00:00:00’&$select=*&$skiptoken=guid’…’
and performs the few necessary updates at the end. This alternative implementation can easily take more than hundred times longer to run and consumes hundreds of API calls compared to the single API call of the original statement without mandatory filtering and the variant using a
The recommended solution when confronted with mandatory filtering is to switch to the
for loop implementation.
More relevant topics on mandatory filtering on Exact Online are: