ACS Email Proofs not sending - Substitution ID

[tommy_8]

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:

anybody experienced a similar behaviour?

thanks in advance, best

Thomas

[ccg1706 · Accepted solution]

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

[Sukrity_Wadhwa]

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!

[tommy_8]

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