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

Avatar

Avatar
Level 1
vbennett
Level 1

Likes

0 likes

Total Posts

8 posts

Correct reply

0 solutions
View profile

Avatar
Level 1
vbennett
Level 1

Likes

0 likes

Total Posts

8 posts

Correct reply

0 solutions
View profile
vbennett
Level 1

29-08-2020

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?

Screen Shot 2020-08-29 at 5.02.23 PM.png

Accepted Solutions (0)

Answers (2)

Answers (2)

Avatar

Avatar
Give Back 50
Employee
Sukrity_Wadhwa
Employee

Likes

158 likes

Total Posts

927 posts

Correct reply

35 solutions
Top badges earned
Give Back 50
Validate 1000
Validate 500
Validate 250
Validate 100
View profile

Avatar
Give Back 50
Employee
Sukrity_Wadhwa
Employee

Likes

158 likes

Total Posts

927 posts

Correct reply

35 solutions
Top badges earned
Give Back 50
Validate 1000
Validate 500
Validate 250
Validate 100
View profile
Sukrity_Wadhwa
Employee

08-09-2020

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.

Avatar

Avatar
Boost 250
MVP
DavidKangni
MVP

Likes

258 likes

Total Posts

372 posts

Correct reply

152 solutions
Top badges earned
Boost 250
Validate 1
Give Back 5
Give Back 3
Give Back 25
View profile

Avatar
Boost 250
MVP
DavidKangni
MVP

Likes

258 likes

Total Posts

372 posts

Correct reply

152 solutions
Top badges earned
Boost 250
Validate 1
Give Back 5
Give Back 3
Give Back 25
View profile
DavidKangni
MVP

31-08-2020

Hi,

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

 

Thanks,

David