Overview

This command will display the list of all capture batches.

Related tables

Table	Description	Primary key (PK)	Connection tables
DCFILECAPTBATCH	Capture batch	CDFILECAPTBATCH
DCCAPTURESETTING	Capture configuration	CDCAPTURESETTING	DCFILECAPTBATCH
DCFILECAPTSTEP	Capture batch steps	CDSTEP	DCFILECAPTBATCH
DCFCBATCHFILE	Batch files	CDBATCHFILE	DCFILECAPTBATCH

Oracle

SELECT
   DCF.FGFILECAPTSTEP,
   DCF.IDFILECAPTBATCH,
   DCF.NMFILECAPTBATCH,
   DCC.NMCAPTURESETTING,
   DCC.IDCAPTURESETTING,
   TO_DATE(TO_CHAR(STARTSTEP.DTSTARTSTEP, 'YYYY-MM-DD')||' '||STARTSTEP.TMSTARTSTEP, 'YYYY-MM-DD HH24:MI') AS STARTSTEP, /*NOTE1*/
   CASE
   WHEN FINISHSTEP.DTSTARTSTEP IS NOT NULL AND FINISHSTEP.TMSTARTSTEP IS NOT NULL THEN
   TO_DATE(TO_CHAR(FINISHSTEP.DTSTARTSTEP, 'YYYY-MM-DD')||' '||FINISHSTEP.TMSTARTSTEP, 'YYYY-MM-DD HH24:MI')
   ELSE NULL
   END  AS FINISHSTEP , /*NOTE2*/
   COALESCE((
   SELECT SUM(COALESCE(DFF.QTPAGES, 1))
   FROM DCFCBATCHFILE DFF
   WHERE DFF.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH), 0
   ) QTFILECAPTURED /*NOTE3*/
FROM DCFILECAPTBATCH DCF
INNER JOIN DCCAPTURESETTING DCC ON (DCC.CDCAPTURESETTING = DCF.CDCAPTURESETTING)
LEFT JOIN (
   SELECT MIN (FCS1.CDSTEP) AS CDSTEPSTART, MAX (FCS2.CDSTEP) AS CDSTEPFINISH, FCB.CDFILECAPTBATCH
   FROM DCFILECAPTBATCH FCB
   LEFT JOIN DCFILECAPTSTEP FCS1 ON (FCB.CDFILECAPTBATCH = FCS1.CDFILECAPTBATCH AND FCS1.FGSTEP = 1 AND FCS1.NRSEQUENCEOWNER IS NULL)
   LEFT JOIN DCFILECAPTSTEP FCS2 ON (FCB.CDFILECAPTBATCH = FCS2.CDFILECAPTBATCH AND FCS2.FGSTEP = 25 AND FCS2.NRSEQUENCEOWNER IS NULL)
   GROUP BY FCB.CDFILECAPTBATCH
) TB ON (TB.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH) /*NOTE4*/
LEFT JOIN DCFILECAPTSTEP STARTSTEP ON (STARTSTEP.CDSTEP = TB.CDSTEPSTART)
LEFT JOIN DCFILECAPTSTEP FINISHSTEP ON (FINISHSTEP.CDSTEP = TB.CDSTEPFINISH)
ORDER BY  DCF.IDFILECAPTBATCH, DCC.IDCAPTURESETTING

Postgres

SELECT
   DCF.FGFILECAPTSTEP,
   DCF.IDFILECAPTBATCH,
   DCF.NMFILECAPTBATCH,
   DCC.NMCAPTURESETTING,
   DCC.IDCAPTURESETTING,
   STARTSTEP.DTSTARTSTEP + STARTSTEP.TMSTARTSTEP::time as STARTSTEP, /*NOTE1*/
   FINISHSTEP.DTSTARTSTEP + FINISHSTEP.TMSTARTSTEP::time as FINISHSTEP, /*NOTE2*/
   COALESCE((
   SELECT SUM(COALESCE(DFF.QTPAGES, 1))
   FROM DCFCBATCHFILE DFF
   WHERE DFF.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH), 0
   ) QTFILECAPTURED /*NOTE3*/
FROM DCFILECAPTBATCH DCF
INNER JOIN DCCAPTURESETTING DCC ON (DCC.CDCAPTURESETTING = DCF.CDCAPTURESETTING)
LEFT JOIN (
   SELECT MIN (FCS1.CDSTEP) AS CDSTEPSTART, MAX (FCS2.CDSTEP) AS CDSTEPFINISH, FCB.CDFILECAPTBATCH
   FROM DCFILECAPTBATCH FCB
   LEFT JOIN DCFILECAPTSTEP FCS1 ON (FCB.CDFILECAPTBATCH = FCS1.CDFILECAPTBATCH AND FCS1.FGSTEP = 1 AND FCS1.NRSEQUENCEOWNER IS NULL)
   LEFT JOIN DCFILECAPTSTEP FCS2 ON (FCB.CDFILECAPTBATCH = FCS2.CDFILECAPTBATCH AND FCS2.FGSTEP = 25 AND FCS2.NRSEQUENCEOWNER IS NULL)
   GROUP BY FCB.CDFILECAPTBATCH
) TB ON (TB.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH) /*NOTE4*/
LEFT JOIN DCFILECAPTSTEP STARTSTEP ON (STARTSTEP.CDSTEP = TB.CDSTEPSTART)
LEFT JOIN DCFILECAPTSTEP FINISHSTEP ON (FINISHSTEP.CDSTEP = TB.CDSTEPFINISH)
ORDER BY  DCF.IDFILECAPTBATCH, DCC.IDCAPTURESETTING

SQL Server

SELECT
   DCF.FGFILECAPTSTEP,
   DCF.IDFILECAPTBATCH,
   DCF.NMFILECAPTBATCH,
   DCC.NMCAPTURESETTING,
   DCC.IDCAPTURESETTING,
   CONVERT(DATETIME,STARTSTEP.DTSTARTSTEP,103) + CAST(STARTSTEP.TMSTARTSTEP AS DATETIME) AS STARTSTEP, /*NOTE1*/
   CONVERT(DATETIME,FINISHSTEP.DTSTARTSTEP,103) + CAST(FINISHSTEP.TMSTARTSTEP AS DATETIME) AS FINISHSTEP, /*NOTE2*/
   COALESCE((
   SELECT SUM(COALESCE(DFF.QTPAGES, 1))
   FROM DCFCBATCHFILE DFF
   WHERE DFF.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH), 0
   ) QTFILECAPTURED /*NOTE3*/
FROM DCFILECAPTBATCH DCF
INNER JOIN DCCAPTURESETTING DCC ON (DCC.CDCAPTURESETTING = DCF.CDCAPTURESETTING)
LEFT JOIN (
   SELECT MIN (FCS1.CDSTEP) AS CDSTEPSTART, MAX (FCS2.CDSTEP) AS CDSTEPFINISH, FCB.CDFILECAPTBATCH
   FROM DCFILECAPTBATCH FCB
   LEFT JOIN DCFILECAPTSTEP FCS1 ON (FCB.CDFILECAPTBATCH = FCS1.CDFILECAPTBATCH AND FCS1.FGSTEP = 1 AND FCS1.NRSEQUENCEOWNER IS NULL)
   LEFT JOIN DCFILECAPTSTEP FCS2 ON (FCB.CDFILECAPTBATCH = FCS2.CDFILECAPTBATCH AND FCS2.FGSTEP = 25 AND FCS2.NRSEQUENCEOWNER IS NULL)
   GROUP BY FCB.CDFILECAPTBATCH
) TB ON (TB.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH) /*NOTE4*/
LEFT JOIN DCFILECAPTSTEP STARTSTEP ON (STARTSTEP.CDSTEP = TB.CDSTEPSTART)
LEFT JOIN DCFILECAPTSTEP FINISHSTEP ON (FINISHSTEP.CDSTEP = TB.CDSTEPFINISH)
ORDER BY  DCF.IDFILECAPTBATCH, DCC.IDCAPTURESETTING

Notes

▪1: In this case, the date and time fields have been grouped in a single field.

▪2: In this case, the date and time fields have been grouped in a single field.

▪3: All captured pages have been added up to form the batch total.

▪4: In this case, this sub-select was necessary to be able to relate to the first and last batch step.

Fields used on the view screen

captura-03