Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Adobe Ramp-up Schedule Implementation in ACS

Clarence_Bunch_
Level 4
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
Adhiyan
Employee
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

Clarence_Bunch_
Level 4
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?

SK82248697
Level 2
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