Highlighted

Adobe Ramp-up Schedule Implementation in ACS

Clarence_Bunch_

14-07-2017

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?

Replies

Highlighted

Adhiyan

Employee

14-07-2017

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

Highlighted

Clarence_Bunch_

17-07-2017

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?

Highlighted

SK82248697

20-09-2018

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