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?
Views
Replies
Total Likes
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
Views
Replies
Total Likes
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?
Views
Replies
Total Likes
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
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies