Add Ticket Search table to Invantive SQL Freshdesk driver

Questions
, ,
2

Starting release 22.0.455, the following code can be used instead of:

  insert into nativeplatformscalarrequests@fdk
  ...
  from   nativeplatformscalarrequests@fdk nst
  join   jsontable
  ...
  where  nst.orig_system_group = l_orig_system_group
  and    nst.result_text != '{"results":[],"total":0}'

New code:

  insert into Tickets@InMemoryStorage
  ( company_id int64
  , subject    varchar2
  , id         int64
  , created_at datetime
  , email      varchar2
  , group_id   int64
  , phone      varchar2
  )
  select tkt.company_id
  ,      tkt.subject    
  ,      tkt.id         
  ,      tkt.created_at 
  ,      tkt.email      
  ,      tkt.group_id   
  ,      tkt.phone      
  from   groups grp  
  join   search_tickets(query => '"group_id:' || grp.id || ' AND created_at:''' || to_char(sysdateutc, 'YYYY-MM-DD') || '''"' ) tkt
  where  grp.name like 'SC%'

The number of API calls needed will be less, typically at approximately 50% of the original runtime, due to only making API calls for pages actually containing data.

Release 22.0.455 will be taken into production on Invantive Cloud typically within 7 calendar days after December 5, 2022.