Itgensql001: Unknown identifier 'unitprice_vat'

Vragen
,
1

De volgende query werkt niet meer sinds de overgang van 17.32 naar 20.0.148:

select salesorder_salesordernumber_attr
,      salesorder_status_attr 
,      salesorder_orderdate
,      salesorder_deliverydate
,      salesorder_description
,      salesorder_yourref
,      salesorder_orderedby_code_attr
,      salesorder_orderedby_name
,      salesorder_orderedby_phone
,      salesorder_orderedby_email
,      salesorder_deliverto_code_attr
,      salesorder_deliverto_name
,      salesorder_deliverto_phone
,      salesorder_deliverto_email
,      salesorder_deliveryaddress_addressline1
,      salesorder_deliveryaddress_postalcode
,      salesorder_deliveryaddress_city
,      salesorder_deliveryaddress_country_code_attr
,      salesorder_invoiceto_code_attr
,      salesorder_invoiceto_name
,      salesorder_invoiceto_phone
,      salesorder_invoiceto_email
,      salesorder_foreignamount_currency_code_attr
,      salesorder_foreignamount_value
,      salesorder_foreignamount_rate
,      line_attr
,      description
,      item_code_attr
,      item_description
,      quantity
,      deliverydate
,      unit_code_attr
,      unit_description
,      unitprice_currency_code_attr
,      unitprice_value
,      unitprice_vat -- COMMENTAAR VAN MAKEN
,      unitprice_vatpercentage
,      netprice_currency_code_attr
,      netprice_value
,      netprice_vat -- COMMENTAAR VAN MAKEN
,      netprice_vatpercentage
,      foreignamount_currency_code_attr
,      foreignamount_value
,      foreignamount_rate
,      foreignamount_vatbaseamount
,      foreignamount_vatamount
,      discountpercentage
,      usedropshipment
,      rownum
from   ExactOnlineXML..Salesorderlines

De foutmelding is:

itgensql001: Unknown identifier ‘unitprice_vat’. Consider one of the following: UNITPRICE_VATPERCENTAGE, UNITPRICE_VALUE, NETPRICE_VALUE.

Als ik de gekenmerkte regels omzet in commentaar werkt het wel.

Hoe kan ik de BTW-gegevens van de verkooporderregels weer terugkrijgen in de resultaten?

Hetzelfde probleem treedt op bij:

select item_issalesitem
,      quantity
,      invoice_ordernumber_attr
,      invoice_invoicenumber_attr
,      invoice_warehouse_code_attr
,      item_id_attr
,      item_code_attr
from   ExactOnlineXML..InvoiceLines
where  quantity <0

voor het veld invoice_warehouse_code_attr, maar dat veld blijkt niet verder verwerkt te worden.

2

Advies is om een query op de XML-tabel SalesInvoiceLines om te zetten naar zoiets als onder, dit voorbeeld te controleren op correctheid en te gebruiken:

select hdr.OrderNumber
,      hdr.status
,      hdr.orderdate
,      hdr.DeliveryDate 
       OrderDeliveryDate
,      hdr.Description
       OrderDescription
,      hdr.YourRef
,      actord.Code
,      actord.Name
,      actord.Phone
,      actord.Email
,      actdlr.Code
,      actdlr.Name
,      actdlr.Phone
,      actdlr.Email
,      adsdlr.AddressLine1
,      adsdlr.PostCode
,      adsdlr.City
,      adsdlr.Country
,      active.Code
,      active.Name
,      active.Phone
,      active.Email
,      sle.Currency
,      hdr.AmountFC
       OrderAmountFC
,      case
       when hdr.AmountDC = 0
       then null
       else hdr.AmountFC / hdr.AmountDC
       end
       OrderExchangeRate
,      sle.LineNumber
,      sle.Description
,      itm.Code
,      itm.Description
,      sle.Quantity
,      sle.DeliveryDate
,      sle.UnitCode
,      uom.Description
       UnitDescription
,      null UnitPriceCurrency
,      sle.UnitPrice
,      null UnitPriceVAT
,      null UnitPriceVATPercentage
,      sle.Currency NetPriceCurrencyCode
,      sle.NetPrice
,      sle.VATCode
,      case
       when sle.Netprice = 0
       then null
       else sle.VATAmount / sle.Netprice * 100
       end
       NetPriceVatPercentage
,      sle.Currency
,      sle.AmountFC
,      case
       when sle.AmountDC = 0
       then null
       else sle.AmountFC / sle.AmountDC
       end
       ExchangeRate
,      sle.AmountFC VATBaseAmount
,      sle.VATAmount VATAmount
,      sle.Discount
,      sle.UseDropShipment
from   SalesOrderLinesIncremental hdr
join   SalesOrderLinesIncremental sle
on     sle.OrderID = hdr.OrderID
and    sle.LineNumber != 0
join   AccountsIncremental actord
on     actord.id = sle.OrderedBy
join   AccountsIncremental actdlr
on     actdlr.id = sle.DeliverTo
join   AccountsIncremental active
on     active.id = sle.InvoiceTo
join   AddressesIncremental adsdlr
on     adsdlr.Id = sle.DeliveryAddress
join   ItemsIncremental itm
on     itm.Id = sle.Item
join   Units uom
on     uom.Division = sle.Division
and    uom.Code     = sle.UnitCode
where  hdr.linenumber = 0
order
by     sle.OrderNumber
,      sle.LineNumber
3

De SQL-query:

select item_issalesitem
,      quantity
,      invoice_ordernumber_attr
,      invoice_invoicenumber_attr
,      invoice_warehouse_code_attr
,      item_id_attr
,      item_code_attr
from   ExactOnlineXML..InvoiceLines
where  quantity <0

kan efficiënter herschreven worden als een query op Exact Online zoals:

select itm.IsSalesItem
,      sil.Quantity
,      sil.OrderNumber
,      sil.InvoiceNumber
,      whe.Code
       WarehouseCode
,      itm.ID
       ItemID
,      itm.Code
       ItemCode
from   SalesInvoiceLinesIncremental sil
join   ItemsIncremental itm
on     itm.ID = sil.Item
join   ExactOnlineREST..Warehouses whe
on     whe.id = sil.Warehouse
where  Quantity < 0
and    sil.LineNumber != 0
4

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.