Itgensql001 error on Data Hub v20.0 with Exact Online XML accounts on column salespaymentcondition_code_attr

Questions
, , ,
1

Upgrading to Data Hub v20.0 from v17.32 … two SQL queries have issues with the new version (v20.0). Both problem queries work fine in the existing version (v17.32). All other queries still work fine.

Query 1. message from log:

2020-11-17 09:20:18.345 Warning itgendhb173: Context: select CODE_ATTR,STATUS_ATTR,NAME,PHONE,EMAIL,ISSUPPLIER,SALES...INE_SALES,LEADPURPOSE_CODE_ATTR from   ExactOnlineXML..Accounts
2020-11-17 09:20:18.408 Error itgencun016: Error itgensql001: itgensql001: Unknown identifier 'SALESPAYMENTCONDITION_CODE_ATTR'. Consider one of the following: CODE_ATTR.
2020-11-17 09:20:20.439 Error itgensql001: ValidationException

Query 2. message from log:

2020-11-17 09:24:29.819 Warning itgendhb173: Context: select DELIVERTO_VATNUMBER,FOREIGNAMOUNT_RATE,FOREIGNAMOUNT_VA...nlineXML..SalesOrders where STATUS_ATTR<>21 and STATUS_ATTR<>45
2020-11-17 09:24:29.866 Error itgencun016: Error itgensql001: itgensql001: Unknown identifier 'SHIPPINGMETHOD_CODE_ATTR'. Consider one of the following: EXACTONLINEXML..SALESORDERS.SALESORDERNUMBER_ATTR, EXACTONLINEXML..SALESORDERS.STATUS_ATTR, EXACTONLINEXML..SALESORDERS.ORDERDATE, EXACTONLINEXML..SALESORDERS.DELIVERYDATE, EXACTONLINEXML..SALESORDERS.DESCRIPTION_TERMID_ATTR.

Update with SQL

Sales Order Query:

select DELIVERTO_VATNUMBER
,      FOREIGNAMOUNT_RATE
,      FOREIGNAMOUNT_VALUE
,      FOREIGNAMOUNT_CURRENCY_CODE_ATTR
,      SALESORDERNUMBER_ATTR
,      DESCRIPTION
,      YOURREF
,      STATUS_ATTR
,      SHIPPINGMETHOD_CODE_ATTR
,      ORDEREDBY_CODE_ATTR
,      ORDERDATE
,      REMARKS
from   ExactOnlineXML..SalesOrders
where  STATUS_ATTR <> 21 
and    STATUS_ATTR <> 45

local export results as "C:\Hantex\Data\Exact_Data\Exact_SalesOrders.csv" format csv

Accounts Query:

select CODE_ATTR
,      STATUS_ATTR
,      NAME
,      PHONE
,      EMAIL
,      ISSUPPLIER
,      SALESPAYMENTCONDITION_CODE_ATTR
,      CREDITLINE_SALES
,      LEADPURPOSE_CODE_ATTR
from   ExactOnlineXML..Accounts

local export results as "C:\Hantex\Data\Exact_Data\Exact_Accounts.csv" format csv
2

Between versions there can be changes in data models and on some platforms (Visma NET, Discourse, Robaws, AFAS, Loket) even without changing version.

Can you add the two SQL queries that raise an error to your question?

3

It seems that a number of columns have been gone that the queries are using. It concerns shipping method and sales condition:

select DELIVERTO_VATNUMBER
,      FOREIGNAMOUNT_RATE
,      FOREIGNAMOUNT_VALUE
,      FOREIGNAMOUNT_CURRENCY_CODE_ATTR
,      SALESORDERNUMBER_ATTR
,      DESCRIPTION
,      YOURREF
,      STATUS_ATTR
--,      SHIPPINGMETHOD_CODE_ATTR
,      ORDEREDBY_CODE_ATTR
,      ORDERDATE
,      REMARKS
from   ExactOnlineXML..SalesOrders@eol
where  STATUS_ATTR <> 21 
and    STATUS_ATTR <> 45

select CODE_ATTR
,      STATUS_ATTR
,      NAME
,      PHONE
,      EMAIL
,      ISSUPPLIER
--,      SALESPAYMENTCONDITION_CODE_ATTR
,      CREDITLINE_SALES
,      LEADPURPOSE_CODE_ATTR
from   ExactOnlineXML..Accounts@eol

The XML-based Exact Online driver is challenging in the sense that there is no documentation which attributes and elements can and/or will ever be provided so a large number of possible XML-branches are excluded. This exclusion list varies somewhat over time to make sure most tables have at most 1.000 columns. The indicated columns have been sacrificed. We will try for the next BETA 20.1.327 to re-establish these (tested and confirmed to be working again). However, since there are many possible side-effects this change when feasible will not be backported to 20.0.

I recommend switching to the Exact Online REST-variants of these tables. For example the first query can be rewritten to something like:

select actdlr.vatnumber
,      case
       when sor.amountdc = 0
       then 1
       else sor.amountfc / sor.amountdc 
       end
       exchangerate
,      sor.amountfc
,      sor.currency
,      sor.ordernumber
,      sor.description
,      sor.yourref
,      sor.status
,      sor.shippingmethod
,      actord.code
,      sor.orderdate
,      sor.remarks
from   exactonlinerest..salesordersbulk@eol sor
join   exactonlinerest..accounts@eol actdlr
on     actdlr.division = sor.division
and    actdlr.id = sor.deliverto
join   exactonlinerest..accounts@eol actord
on     actord.division = sor.division
and    actord.id = sor.orderedby
where  sor.status not in (21, 45)

Also, the REST-variants are nowadays in general better performing than the XML-variant since filters such as sor.status not in (21, 45) are forwarded to Exact Online, possibly reducing the data volume significantly.