EBIFour.com > Training > Clarify Tips > Postgre:Retrieve Transaction Activity Based on Date Range

Postgre:Retrieve Transaction Activity Based on Date Range

24th April 2021

In a previous post, we reviewed the Cleo Clarify Postgre Log of Message table.

Below we show Clarify users how to use the Postgre tables: RELATED_LOG and LOG_OF_COLLABORATION to retrieve aggregate inbound/outbound EDI transaction history.

Postgre SQL select statement

SELECT * FROM “EBI”.“RELATED_LOG” “R”
JOIN “EBI”.“LOG_OF_COLLABORATION” “LC” ON “R”.“RELATED_LOG_ID” = “LC”.“ID”
LEFT JOIN “EBI_USER”.“GROUPED_MESSAGE_LOG” “M” ON “M”.“MESSAGE_LOG_ID” = “R”.“LOG_ID”
AND “R”.“LOG_TYPE” = 50331649
AND “M”.“INTERCHANGE_CONTROL_REF” = ‘1’
AND “M”.“GROUP_CONTROL_REFERENCE” = '1’
where “CREATED_DATE” between '2021-01-01 00:00:01’ and '2020-04-24 23:59:59’
order by “CREATED_DATE” desc;


By: on