Error in SFTP File Processing Workflow (FPW) with Insert/Update Step

[#1]

Hey All,

I am trying to work through an issue with an SFTP File Processing Workflow.

We are trying to grab the data from the SFTP from our Shopify Customer Orders. In doing so I keep encountering errors in the Insert/Update step & I am not sure where the disconnect is. 

The Error is:

PGS-220000 PostgreSQL error: ERROR: column w0.morderlineitemkey does not exist LINE 1: ...N CusShopify C1 ON (C1.sOrderlineitemkey = substr(W0.mOrderL... ^ HINT: Perhaps you meant to reference the column "w0.sorderlineitemkey".

Does anyone have an idea what this could be?

[vbennett]

Hi @vbennett,

Were you able to resolve this problem? Please let us know.

Thanks!

[vbennett]

hi, no I still haven't. I am wondering if it's the sftp file because I have confirmed all the sftp data columns are the same as the ACS fields. Here is the latest error code: WDB-200001 SQL statement 'INSERT INTO wkf50169430_244_1 (iRecTrnState, iPKey1, sPKey_1, fFld1952776435, sFld1952841972, sFld1952841973, sFld1952841974, sFld1952841975, sFld1952841976, sFld1952841977, sFld1952841978, sFld1952841979, sFld1952841980, sFld1952841981, sFld1952841982, sFld1952841983, sFld1952841984, sFld1952841985, sFld1952841986, sFld1952841987, tsFld1952841988, sFld1952841990, sFld1952907527, sFld1952907528, fFld1952907529, sFld1952907530, sFld1952907531, sFld1952907532, sFld1952907533, sFld1952907534, iFld1952907535, sFld1952907536, fFld1952907537, dFld1952907538, fFld1952907539, sFld1952907540, sFld1952907541, sFld1952907542, sFld1952973079, sFld1952973082, sFld1952973080, sFld1952973081, sFld1952973083, sFld1952973084, sFld1952973085, sFld1952973087, sFld1952973086, sFld1952973088, sFld1952973089, sFld1952973090, sFld1958281507, sFld2007040292, sFld2018378021, sFld2030305574, biFld2034893095, sFld2036531496, tsFld1906641214, tsSrc69, sSrc68, sSrc67, sSrc66, sSrc65, sSrc64, sSrc63, sSrc62, sSrc61, sSrc60, sSrc59, sSrc58, sSrc57, sSrc56, sSrc55, sSrc54, sSrc53, sSrc52, sSrc51, sSrc50, sSrc49, sSrc48, sSrc47, sSrc46, sSrc45, sSrc44, sSrc43, sSrc42, sSrc41, sSrc40, sSrc39, sSrc38, sSrc37, sSrc36, sSrc35, sSrc34, biSrc33, sSrc32, sSrc31, sSrc30, sSrc29, biSrc28, biSrc27, fSrc26, fSrc25, fSrc24, fSrc23, biSrc22, biSrc21, iSrc20, tsSrc19, dSrc18, fSrc17, biSrc16, sSrc15, sSrc14, sSrc13, biSrc12, biSrc11, sSrc10, sSrc9, iSrc8, sSrc7, biSrc6, biSrc5, biSrc4, biSrc3, sSrc2, iSrc1) SELECT 0, case when W0.iLineNum <> 0 then W0.iLineNum else NULL end, C1.sOrderlineitemkey, W0.fActualPrice, substr(W0.sBillingCountryCode, 1, 255), substr(W0.sBillingAddress1, 1, 255), substr(W0.sBillingCity, 1, 255), substr(W0.sBillingCompany, 1, 255), substr(W0.sBillingAddressID, 1, 255), substr(W0.sBillingAddress2, 1, 255), substr(W0.sBillingCountryName, 1, 255), substr(W0.sBillingFirstName, 1, 255), substr(W0.sBillingLastName, 1, 255), substr(W0.sBillingLatitude, 1, 255), substr(W0.sBillingLongitude, 1, 255), substr(W0.sBillingPhone, 1, 255), substr(W0.sBillingProvinceCode, 1, 255), substr(W0.sBillingProvinceName, 1, 255), substr(W0.sCheckoutEmail, 1, 255), substr(W0.biCheckoutHeaderKey, 1, 127), W0.tsCreatedDatetime, substr(W0.biCustomerID, 1, 255), substr(W0.biCustomerKey, 1, 255), substr(W0.sCustomerLastName, 1, 255), W0.fDiscountAmount, substr(W0.biOrderHeaderKey, 1, 127), substr(W0.biOrderID, 1, 127), substr(W0.biOrderKey, 1, 127), substr(W0.iOrderLineItemKey, 1, 127), substr(W0.sOrderName, 1, 255), W0.iOrderNumber, substr(W0.sOrderStatus, 1, 255), W0.fOrderSubTotal, W0.dOrderTotalPrice, W0.fPrice, substr(W0.biProductKey, 1, 127), substr(W0.sShippingAddress1, 1, 255), substr(W0.sShippingAddress2, 1, 255), substr(W0.sShippingAddressID, 1, 255), substr(W0.sShippingCountryCode, 1, 255), substr(W0.sShippingCity, 1, 255), substr(W0.sShippingCompany, 1, 255), substr(W0.sShippingCountryName, 1, 255), substr(W0.sShippingFirstName, 1, 255), substr(W0.sShippingLastName, 1, 255), substr(W0.sShippingLongitude, 1, 255), substr(W0.sShippingLatitude, 1, 255), substr(W0.sShippingPhone, 1, 255), substr(W0.sShippingProvinceCode, 1, 255), substr(W0.sShippingProvinceName, 1, 255), substr(W0.sCustomerEmail, 1, 255), substr(W0.sBillingZipCode, 1, 255), substr(W0.sCustomerFirstName, 1, 255), substr(W0.biOrderLineItemID, 1, 127), W0.biOrderTotalQuantity, substr(W0.sShippingZipCode, 1, 255), W0.tsLastUpdateDateTime, W0.tsLastUpdateDateTime, W0.sShippingCountryName, W0.sShippingCountryCode, W0.sShippingProvinceName, W0.sShippingProvinceCode, W0.sShippingZipCode, W0.sShippingCity, W0.sShippingLongitude, W0.sShippingLatitude, W0.sShippingAddress2, W0.sShippingAddress1, W0.sShippingAddressID, W0.sShippingPhone, W0.sShippingLastName, W0.sShippingFirstName, W0.sShippingCompany, W0.sBillingCountryName, W0.sBillingCountryCode, W0.sBillingProvinceName, W0.sBillingProvinceCode, W0.sBillingZipCode, W0.sBillingCity, W0.sBillingLongitude, W0.sBillingLatitude, W0.sBillingAddress2, W0.sBillingAddress1, W0.sBillingAddressID, W0.sBillingPhone, W0.sBillingLastName, W0.sBillingFirstName, W0.sBillingCompany, W0.sCheckoutEmail, W0.sOrderContactKey, W0.sVariantURL, W0.sFullVariantTitle, W0.sVariantID, W0.biVariantSKU, W0.sProductURL, W0.sProductTags, W0.sProductDescription, W0.sProductTitle, W0.biProductID, W0.biProductKey, W0.fPrice, W0.fDiscountAmount, W0.fLineItemPrice, W0.fActualPrice, W0.biLineItemQuantity, W0.biOrderLineItemID, W0.iOrderLineItemKey, W0.tsCreatedDatetime, W0.dOrderTotalPrice, W0.fOrderSubTotal, W0.biOrderTotalQuantity, W0.sCustomerEmail, W0.sCustomerLastName, W0.sCustomerFirstName, W0.biCustomerID, W0.biCustomerKey, W0.sOrderStatus, W0.sToken, W0.iOrderNumber, W0.sOrderName, W0.biOrderID, W0.biOrderKey, W0.biCheckoutHeaderKey, W0.biOrderHeaderKey, W0.sSeasonKey, W0.iLineNum FROM wkf50169430_235_3 W0 LEFT JOIN CusShopify C1 ON (C1.sOrderlineitemkey = substr(W0.iOrderLineItemKey, 1, 127))' could not be executed.

[#4]

Hi,

Check if you column orderlineitemkey exists and then the format.  It's looking for a text value.

Thanks,

David