Transactions journal detail info

SET show_deleted_records = 1;
SELECT j.xid, j.objid,
(b.EPOCH + (FLOAT8(j.entry_ts) * b.ONE_MICRO)) optime,
o.opname,
data.cnt, data.action
FROM _vt_txjournal j
LEFT JOIN _vt_txjoperation o ON j.operation = o.operation
LEFT JOIN
(
SELECT createxid xid, COUNT(*) cnt, ‘IU’ action
FROM EMPLOYEE t
GROUP BY createxid
UNION ALL
SELECT t1.deletexid, COUNT(*), ‘D’ action
FROM EMPLOYEE t1
LEFT JOIN EMPLOYEE t2 ON t1.deletexid = t2.createxid
WHERE t1.deletexid > 0
AND t2.createxid IS NULL
GROUP BY t1.deletexid
) data ON j.xid = data.xid
CROSS JOIN _vt_pg_time_offset b
WHERE j.objid = (SELECT o.objid
FROM _t_object o
INNER JOIN _t_object d ON o.objdb = d.objid AND d.objname = CURRENT_CATALOG
AND o.objname = ‘EMPLOYEE’
)
OR
j.objid = 0 AND j.xid = data.xid
ORDER BY j.xid, j.entry_ts;

SET show_deleted_records = 1;
SELECT j.xid, j.objid,
(b.EPOCH + (FLOAT8(j.entry_ts) * b.ONE_MICRO)) optime,
o.opname,
CASE j.operation WHEN 34 THEN dc.cnt WHEN 33 THEN dd.cnt END cnt,
CASE j.operation WHEN 34 THEN ‘IU’ WHEN 33 THEN ‘D’ END action
FROM _vt_txjournal j
LEFT JOIN _vt_txjoperation o ON j.operation = o.operation
LEFT JOIN
(
SELECT createxid xid, COUNT(*) cnt
FROM EMPLOYEE t
GROUP BY createxid
) dc ON j.xid = dc.xid
LEFT JOIN
(
SELECT deletexid xid, COUNT(*) cnt
FROM EMPLOYEE t
WHERE deletexid > 0
GROUP BY deletexid
) dd ON j.xid = dd.xid
CROSS JOIN _vt_pg_time_offset b
WHERE j.objid = (SELECT o.objid
FROM _t_object o
INNER JOIN _t_object d ON o.objdb = d.objid AND d.objname = CURRENT_CATALOG
AND o.objname = ‘EMPLOYEE’
) OR
j.objid = 0 AND j.xid IN (dc.xid, dd.xid)
ORDER BY j.xid, j.entry_ts;