odataexact-onlineinvantive-sql

Handcrafted OData queries on Exact Online with Invantive


We are currently running a number of hand-crafted and optimized OData queries on Exact Online using Python. This runs on several thousand of divisions. However, I want to migrate them to Invantive SQL for ease of maintenance.

But some of the optimizations like explicit orderby in the OData query are not forwarded to Exact Online by Invantive SQL; they just retrieve all data or the top x and then do an orderby.

Especially for maximum value determination that can be a lot slower.

Simple sample on small table:

https://start.exactonline.nl/api/v1/<<division>>/financial/Journals?$select=BankAccountIBAN,BankAccountDescription&$orderby=BankAccountIBAN desc&$top=5

Is there an alternative to optimize the actual OData queries executed by Invantive SQL?


Solution

  • You can either use the Data Replicator or send the hand-craft OData query through a native platform request, such as:

    insert into NativePlatformScalarRequests
    ( url
    , orig_system_group
    ) 
    select replace('https://start.exactonline.nl/api/v1/{division}/financial/Journals?$select=BankAccountIBAN,BankAccountDescription&$orderby=BankAccountIBAN desc&$top=5', '{division}', code) 
    ,      'MYSTUFF-' || code
    from   systempartitions@datadictionary 
    limit  100 /* First 100 divisions. */
    
    create or replace table exact_online_download_journal_top5@inmemorystorage
    as
    select jte.*
    from   ( select npt.result 
             from   NativePlatformScalarRequests npt 
             where  npt.orig_system_group like 'MYSTUFF-%'
             and    npt.result is not null
    ) npt
    join   jsontable
           ( null 
             passing npt.result 
             columns BankAccountDescription varchar2 path 'd[0].BankAccountDescription'
             ,       BankAccountIBAN varchar2 path 'd[0].BankAccountIBAN'
           ) jte
    

    From here on you can use the in memory table, such as:

    select * from exact_online_download_journal_top5@inmemorystorage
    

    But of course you can also 'insert into sqlserver'.