Een sql script dat voorheen goed werkte voor het ophalen van artikelen uit Exact Online werkt niet meer door foutmelding itgensql151. Volledige foutmelding: ‘Ontledingsfout op regel 46 in kolom 61: Onbekend veld of parameter ‘ITEMEXTRAFIELDVALUES.ITEMID’. Mogelijke geldige alternatieve symboolnamen: ItemID, ID, IsTime, Unit, Created, ItemGroup, Notes, Picture, IsMakeItem.
het script dat het probleem geeft:
BEGIN
– Return output as CSV.
cloud_http.set_response_content_type(‘text/csv’);cloud_http.set_response_header_value(‘Content-Disposition’, ‘attachment; filename=“items.csv”’);
– Append JSON form of table contents to HTTP output.
FOR r IN(SELECTMAX(CASE WHEN ItemExtraFieldValues.Description = ‘Owner’ THEN ItemExtraFieldValues.Value END) AS Owner,Item.Description AS name,Item.Code,Item.StatisticalCode AS cn_code,MAX(CASE WHEN ItemExtraFieldValues.Description = ‘Type’ THEN ItemExtraFieldValues.Value END) AS Type,Item.Barcode AS ean_code,MAX(CASE WHEN ItemExtraFieldValues.Description = ‘minimale bestelhoeveelheid’ THEN ItemExtraFieldValues.Value END) AS min,MAX(CASE WHEN ItemExtraFieldValues.Description = ‘Maximale bestelhoeveelheid’ THEN ItemExtraFieldValues.Value END) AS max,MAX(CASE WHEN ItemExtraFieldValues.Description = ‘Bestellen per’ THEN ItemExtraFieldValues.Value END) AS step,MAX(SalesItemPrices.Price) AS price,MAX(SalesItemPrices.Price) AS start_price,MAX(SalesItemPrices.Price) AS margin_price,MAX(SalesItemPrices.Price) AS neutral_price,MAX(CASE WHEN ItemExtraFieldValues.Description = ‘Adviesprijs’ THEN REPLACE(ItemExtraFieldValues.Value, ‘,’, ‘.’) END) AS advice_price,Item.ExtraDescription label ‘description’,-- Wrapped CASE statement in MAX for proper aggregation
MAX(
CASE
WHEN Item.StartDate <= sysdate AND (Item.EndDate IS NULL OR Item.EndDate >= sysdate) THEN LOWER(true)
ELSE LOWER(false)
END
) AS active,
MAX(CASE WHEN ItemExtraFieldValues.Description = 'Aantal per doos' THEN ItemExtraFieldValues.Value END) AS copies_package,
MAX(CASE WHEN ItemExtraFieldValues.Description = 'Aantal in omdoos' THEN ItemExtraFieldValues.Value END) AS copies_outer_box,
MAX(CASE WHEN ItemExtraFieldValues.Description = 'Aantal op pallet' THEN ItemExtraFieldValues.Value END) AS copies_per_pallet,
MAX(CASE WHEN ItemExtraFieldValues.Description = 'Verzenden naar Prodist' THEN LOWER(ItemExtraFieldValues.Value) END) AS prodist,
LOWER(false) AS studio,
Item.IsFractionAllowedItem LABEL 'shared'
FROM
itemsincremental@eol Item
LEFT JOIN
ItemExtraFieldValues ON Item.ID = ItemExtraFieldValues.ItemId
LEFT JOIN
SalesItemPrices ON Item.ID = SalesItemPrices.Item
WHERE
Item.Modified >= trunc(sysdate)
AND Item.Modified < trunc(sysdate) + 2
AND ItemExtraFieldValues.Value <> 'EXCLUDE'
AND (Item.EndDate IS NULL OR Item.EndDate >= trunc(sysdate))
GROUP BY
Item.Description, Item.Code, Item.StatisticalCode, Item.Barcode, Item.ExtraDescription, Item.IsFractionAllowedItem
FOR CSV
, column delimiter ';'
)
LOOP
cloud_http.append_to_response_body_text(r.csv);
END
LOOP;
END;
Het herschrijven van de kolomnamen in caps lost het probleem helaas niet op.