Expand my Community achievements bar.

We are excited to introduce our latest innovation to enhance the Adobe Campaign user experience — the Adobe Campaign v8 Web User Interface!

Adobe Ramp-up Schedule Implementation in ACS

Avatar

Level 4

Hi,

I received an Deliverability Audit from Adobe with a Ramp-Up Schedule.  I found these directions for implementing it...Computing the sending date .  While going through these steps for Eastern Time.  I get an error...

PGS-220000 PostgreSQL error: ERROR: column "tsevent" is of type timestamp with time zone but expression is of type character varying
LINE 1: ...geWeight) SELECT COALESCE(N1.iRecipientId , 0), W2.sSegmen...
^
HINT: You will need to rewrite or cast the expression.

and

WDB-200001 SQL statement 'INSERT INTO wkDlv_5043974T (iProfileId, tsEvent, sTargetCode, sVariant, iDeliveryId, iFcp, iInboxRendering, iTargetDataId, sAddress, iFormat, iBlackList, dMessageWeight) SELECT COALESCE(N1.iRecipientId , 0), W2.sSegmentCode, E'__MAIN__', NULL, 5043974, 0, 0, W0.iTargetDataId, Lower(N1.sEmail), N1.iEmailFormat, case when N1.iBlackList <> 0 then 1 else N1.iBlackListEmail end, 5 FROM wkf5038183_20_1_ALL W0, NmsRecipient N1 JOIN wkf5038183_20_1_ALL W2 ON (W2.iId = N1.iRecipientId) JOIN NmsGeoUnit G3 ON (G3.iGeoUnitId = 1) JOIN NmsGeoUnit N4 ON (N4.iGeoUnitId = N1.iGeoUnitId) JOIN NmsOrgUnit O5 ON (O5.iOrgUnitId = 1) JOIN NmsOrgUnit N6 ON (N6.iOrgUnitId = N1.iOrgUnitId) WHERE ((N1.iRecipientId = W0.iId) AND (W2.iTargetDataId = W0.iTargetDataId)) AND (((G3.sFullName LIKE COALESCE(COALESCE(N4.sFullName , E'#'), '') || E'%' ESCAPE E'\\' or N4.sFullName LIKE COALESCE(G3.sFullName, '') || E'%' ESCAPE E'\\') AND (O5.sFullName LIKE COALESCE(COALESCE(N6.sFullName , E'#'), '') || E'%' ESCAPE E'\\' or N6.sFullName LIKE COALESCE(O5.sFullName, '') || E'%' ESCAPE E'\\')))' could not be executed.

How do I resolve this?

3 Replies

Avatar

Employee

Hello Clarence,

The error simply means a string value is being passed into the delivery for a column of type=datetime .

The delivery expects the column tsEvent to be of a known date format but it's being calculated as a string. May be the date format is not getting recognized or it's actually passing a string type value . It will be more clear if you can check the data by opening the transition just before the delivery and see what actual data is being fed to the delivery

Regards,
Adhiyan

Avatar

Level 4

Adhiyan,

I have checked the data by opening the transition.  I can see the values in the Segment Code.  I can see recipients but I am still seeing the error.

PGS-220000 PostgreSQL error: ERROR: column "tsevent" is of type timestamp with time zone but expression is of type character varying
LINE 1: ...geWeight) SELECT COALESCE(N1.iRecipientId , 0), W2.sSegmen...
^
HINT: You will need to rewrite or cast the expression.

and

WDB-200001 SQL statement 'INSERT INTO wkDlv_5043974T (iProfileId, tsEvent, sTargetCode, sVariant, iDeliveryId, iFcp, iInboxRendering, iTargetDataId, sAddress, iFormat, iBlackList, dMessageWeight) SELECT COALESCE(N1.iRecipientId , 0), W2.sSegmentCode, E'__MAIN__', NULL, 5043974, 0, 0, W0.iTargetDataId, Lower(N1.sEmail), N1.iEmailFormat, case when N1.iBlackList <> 0 then 1 else N1.iBlackListEmail end, 5 FROM wkf5038183_20_1_ALL W0, NmsRecipient N1 JOIN wkf5038183_20_1_ALL W2 ON (W2.iId = N1.iRecipientId) JOIN NmsGeoUnit G3 ON (G3.iGeoUnitId = 1) JOIN NmsGeoUnit N4 ON (N4.iGeoUnitId = N1.iGeoUnitId) JOIN NmsOrgUnit O5 ON (O5.iOrgUnitId = 1) JOIN NmsOrgUnit N6 ON (N6.iOrgUnitId = N1.iOrgUnitId) WHERE ((N1.iRecipientId = W0.iId) AND (W2.iTargetDataId = W0.iTargetDataId)) AND (((G3.sFullName LIKE COALESCE(COALESCE(N4.sFullName , E'#'), '') || E'%' ESCAPE E'\\' or N4.sFullName LIKE COALESCE(G3.sFullName, '') || E'%' ESCAPE E'\\') AND (O5.sFullName LIKE COALESCE(COALESCE(N6.sFullName , E'#'), '') || E'%' ESCAPE E'\\' or N6.sFullName LIKE COALESCE(O5.sFullName, '') || E'%' ESCAPE E'\\')))' could not be executed.

Is there anything else, I can look for?  I performed the direction, exactly like this URL:  https://docs.campaign.adobe.com/doc/standard/en/SEN_Sheduling_messages_Computing_the_sending_date.ht...

What am I missing?

Avatar

Level 2

Hi Clarence,

How did you get this issue resolved? I am getting the same error while doing import of data into ACS via flat file and Here is the information about issue what i am seeing - ACS File Import Error

Any help is appreciated.

Thanks

Sathees