Expand my Community achievements bar.

SOLVED

ACS Email Proofs not sending - Substitution ID

Avatar

Level 2

hey ACS users,

 

we're getting an error when trying to send proofs for a specific delivery...

 

Error messages:

WDB-200001 SQL statement 'INSERT INTO wkf123274640_1_1 (biMessageId,sAddress,iFormat,sVariant,sTargetCode,iStatus,iBlackList,iProfileId,iProfileId1,sProfileSubstitutionPrefix,iProfileGender,sProfileFirstName,sProfileLastName,sCusLinkProfileAgencyName01,sCusLinkProfileAgencyName02,sCusLinkProfileAgencyStreet,sCusLinkProfileAgencyZipCode,sCusLinkProfileAgencyCity,sCusLinkProfileAgencyEmail,sPhoneNumber,tsEvent,iDeliveryId,tsExpiration) SELECT W0.biMessageId, W0.sAddress, W0.iFormat, W0.sVariant, W0.sTargetCode, W0.iStatus, W0.iBlackList, W0.iProfileId, N1.iRecipientId, P2.sPrefix, N1.iGender, N1.sFirstName, N1.sLastName, C5.sName01, C5.sName02, C5.sStreet, C5.sZipCode, C5.sCity, C5.sEmail, C5.sPhoneNumber, W0.tsEvent, 123270913, NULL FROM wkDlv_123270913T W0 LEFT JOIN NmsRecipient N1 ON (N1.iRecipientId = W0.iProfileId) LEFT JOIN NmsProfileSubstitution P2 ON (P2.iProfileSubstitutionId = W0.iProfileSubstitutionId) JOIN wkf122737358_query2 W3 ON (W3.iId = W0.iProfileId) JOIN NmsRecipient N4 ON (N4.iRecipientId = W3.iId) LEFT JOIN CusDto_agency C5 ON (C5.AgencyNo = N4.CusDtoLastAgency) WHERE (W0.iFailureReason = 0) AND (W0.iFcp <> 0 OR W0.iInboxRendering <> 0)' could not be executed.

 

PGS-220000 PostgreSQL error: ERROR: relation "wkf122737358_query2" does not exist LINE 1: ...eSubstitutionId = W0.iProfileSubstitutionId) JOIN wkf1227373... ^

 

seems like the profile substitution isn't working.

however, we don't use that option in the proof settings:

 

tommy_8_0-1724751264215.png

 

anybody experienced a similar behaviour?

 

thanks in advance, best

Thomas

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 6

Hi @tommy_8 ,

 

The SQL error occurs because the query is trying to insert data into a temporary table while joining other tables, but one table ( "wkf122737358_query2") does not exist or is incorrectly referenced. Despite profile substitution is not enabled, the query still attempts to join with the ("NmsProfileSubstitution") table, showing possible residual settings or misconfigurations.

 

To solve the error, review the workflow and ensure al queries and temporary tables are correctly set up and referenced. Double-check profile substitution settings, even if not enabled, and examine target mapping for issues with the profile selection. Clear cache and restart the workflow to remove any issues. Finally, take a look to the database logs for any underlying issues that might affect query execution.

 

Hope this helps!

 

Regards, 

Celia

 

 

View solution in original post

3 Replies

Avatar

Correct answer by
Level 6

Hi @tommy_8 ,

 

The SQL error occurs because the query is trying to insert data into a temporary table while joining other tables, but one table ( "wkf122737358_query2") does not exist or is incorrectly referenced. Despite profile substitution is not enabled, the query still attempts to join with the ("NmsProfileSubstitution") table, showing possible residual settings or misconfigurations.

 

To solve the error, review the workflow and ensure al queries and temporary tables are correctly set up and referenced. Double-check profile substitution settings, even if not enabled, and examine target mapping for issues with the profile selection. Clear cache and restart the workflow to remove any issues. Finally, take a look to the database logs for any underlying issues that might affect query execution.

 

Hope this helps!

 

Regards, 

Celia

 

 

Avatar

Administrator

Hi @tommy_8,

Was the given solution by @ccg1706 helpful to resolve your query or do you still need more help here? Do let us know.

Thanks!



Sukrity Wadhwa

Avatar

Level 2

hi @Sukrity_Wadhwa @ccg1706 

we are still in bug fixing mode.

However the hint was quite helpful in order to take some steps back. It's obviously not a problem of the delivery.

Thanks, Thomas