Visão geral

Este comando apresentará uma lista de todos os lotes de captura.

Tabelas relacionadas

Tabela	Descrição	Chave primária (PK)	Tabelas de ligação
DCFILECAPTBATCH	Lote de captura	CDFILECAPTBATCH
DCCAPTURESETTING	Configuração de captura	CDCAPTURESETTING	DCFILECAPTBATCH
DCFILECAPTSTEP	Etapas do lote de captura	CDSTEP	DCFILECAPTBATCH
DCFCBATCHFILE	Arquivos do lote	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, /*OBS1*/
   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 , /*OBS2*/
   COALESCE((
   SELECT SUM(COALESCE(DFF.QTPAGES, 1))
   FROM DCFCBATCHFILE DFF
   WHERE DFF.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH), 0
   ) QTFILECAPTURED /*OBS3*/
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) /*OBS4*/
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, /*OBS1*/
   FINISHSTEP.DTSTARTSTEP + FINISHSTEP.TMSTARTSTEP::time as FINISHSTEP, /*OBS2*/
   COALESCE((
   SELECT SUM(COALESCE(DFF.QTPAGES, 1))
   FROM DCFCBATCHFILE DFF
   WHERE DFF.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH), 0
   ) QTFILECAPTURED /*OBS3*/
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) /*OBS4*/
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, /*OBS1*/
   CONVERT(DATETIME,FINISHSTEP.DTSTARTSTEP,103) + CAST(FINISHSTEP.TMSTARTSTEP AS DATETIME) AS FINISHSTEP, /*OBS2*/
   COALESCE((
   SELECT SUM(COALESCE(DFF.QTPAGES, 1))
   FROM DCFCBATCHFILE DFF
   WHERE DFF.CDFILECAPTBATCH = DCF.CDFILECAPTBATCH), 0
   ) QTFILECAPTURED /*OBS3*/
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) /*OBS4*/
LEFT JOIN DCFILECAPTSTEP STARTSTEP ON (STARTSTEP.CDSTEP = TB.CDSTEPSTART)
LEFT JOIN DCFILECAPTSTEP FINISHSTEP ON (FINISHSTEP.CDSTEP = TB.CDSTEPFINISH)
ORDER BY  DCF.IDFILECAPTBATCH, DCC.IDCAPTURESETTING

Observações

▪1: Neste caso foi agrupado os campos de data e hora em um único campo.

▪2: Neste caso foi agrupado os campos de data e hora em um único campo.

▪3: Feita a soma de todas as páginas capturadas para se ter o total do lote.

▪4: Neste caso, foi necessário este sub-select para que fosse possível relacionar com a primeira e última etapa do lote.

Campos utilizados na consulta

captura-03