I have this main query de PL SQL DEVELOER DE ALLROUND AUTOMATION

SELECT COUNT(0) AS CANT,

C.C26NOMBRE AS "CONSOLIDADORA",c.c26feccortenov.

FROM T70OBLIGACION A

JOIN T26PAGADURIA B ON A.C70PAGADURIA = B.C26IDPAGADURIA AND

A.C70CODSUCURSAL = B.C26CODSUCURSALJOIN T26PAGADURIA C ON C.C26IDPAGADURIA = B.C26IDPAGCONSOL AND

C.C26CODSUCURSAL = B.C26SUCURSALCONSOL

WHERE a.C70FECHADESEM BETWEEN

CASE

WHEN EXTRACT(DAY FROM B.C26FECCORTENOV) <= EXTRACT(DAY FROM sysdate)

THEN ADD_MONTHS(B.C26FECCORTENOV, -2)

ELSE ADD_MONTHS(B.C26FECCORTENOV, -1)

END

AND

CASE

WHEN EXTRACT(DAY FROM B.C26FECCORTENOV) > EXTRACT(DAY FROM sysdate) THEN

B.C26FECCORTENOV-1

ELSE ADD_MONTHS(B.C26FECCORTENOV, -1)- 1

END

AND A.C70ESTADO NOT IN ('99,140,120')

GROUP BY C.C26NOMBRE,c.c26feccortenov


 

and I want to be able to exclude from its results those records that appear in the query

SELECT COUNT(0) AS CANT,

C.C26NOMBRE AS "CONSOLIDADORA",c.c26feccortenov.

FROM T70OBLIGACION A

JOIN T26PAGADURIA B ON A.C70PAGADURIA = B.C26IDPAGADURIA AND

A.C70CODSUCURSAL = B.C26CODSUCURSALJOIN T26PAGADURIA C ON C.C26IDPAGADURIA = B.C26IDPAGCONSOL AND

C.C26CODSUCURSAL = B.C26SUCURSALCONSOL

WHERE a.C70FECHADESEM BETWEEN

CASE

WHEN EXTRACT(DAY FROM B.C26FECCORTENOV) <= EXTRACT(DAY FROM sysdate)

THEN ADD_MONTHS(B.C26FECCORTENOV, -2)

ELSE ADD_MONTHS(B.C26FECCORTENOV, -1)

END

AND

CASE

WHEN EXTRACT(DAY FROM B.C26FECCORTENOV) > EXTRACT(DAY FROM sysdate) THEN

B.C26FECCORTENOV-1

ELSE ADD_MONTHS(B.C26FECCORTENOV, -1)- 1

END

AND A.C70ESTADO NOT IN ('99,140,120')

GROUP BY C.C26NOMBRE,c.c26feccortenov

I cannot do it since the data does not match (I have verified by crossing in Excel between the files that each query generates)

This is the query that tried to unify

SELECT COUNT(0) AS CANT,
       C.C26NOMBRE AS "CONSOLIDADORA",
       C.C26FECCORTENOV
FROM T70OBLIGACION A
JOIN T26PAGADURIA B ON A.C70PAGADURIA = B.C26IDPAGADURIA 
                     AND A.C70CODSUCURSAL = B.C26CODSUCURSAL
JOIN T26PAGADURIA C ON C.C26IDPAGADURIA = B.C26IDPAGCONSOL 
                     AND C.C26CODSUCURSAL = B.C26SUCURSALCONSOL
WHERE A.C70FECULTPAGO BETWEEN
      CASE
          WHEN EXTRACT(DAY FROM B.C26FECCORTENOV) <= EXTRACT(DAY FROM sysdate) THEN ADD_MONTHS(B.C26FECCORTENOV, -2)
          ELSE ADD_MONTHS(B.C26FECCORTENOV, -1)
      END
      AND
      CASE
          WHEN EXTRACT(DAY FROM B.C26FECCORTENOV) > EXTRACT(DAY FROM sysdate) THEN B.C26FECCORTENOV - 1
          ELSE ADD_MONTHS(B.C26FECCORTENOV, -1) - 1
      END
      AND A.C70ESTADO IN ('99', '140', '120')
      AND NOT EXISTS (
          SELECT 1
          FROM T70OBLIGACION A2
          JOIN T26PAGADURIA B2 ON A2.C70PAGADURIA = B2.C26IDPAGADURIA 
                              AND A2.C70CODSUCURSAL = B2.C26CODSUCURSAL
          JOIN T26PAGADURIA C2 ON C2.C26IDPAGADURIA = B2.C26IDPAGCONSOL 
                              AND C2.C26CODSUCURSAL = B2.C26SUCURSALCONSOL
          WHERE A2.C70FECHADESEM BETWEEN
                CASE
                    WHEN EXTRACT(DAY FROM B2.C26FECCORTENOV) <= EXTRACT(DAY FROM sysdate) THEN ADD_MONTHS(B2.C26FECCORTENOV, -2)
                    ELSE ADD_MONTHS(B2.C26FECCORTENOV, -1)
                END
                AND
                CASE
                    WHEN EXTRACT(DAY FROM B2.C26FECCORTENOV) > EXTRACT(DAY FROM sysdate) THEN B2.C26FECCORTENOV - 1
                    ELSE ADD_MONTHS(B2.C26FECCORTENOV, -1) - 1
                END
                AND A2.C70ESTADO NOT IN ('99', '140', '120%')
          AND C.C26IDPAGCONSOL = C2.C26IDPAGCONSOL
          AND C.C26SUCURSALCONSOL = C2.C26SUCURSALCONSOL
      )
GROUP BY C.C26NOMBRE, C.C26FECCORTENOV

This is an example of the table t26pagaduria

Note that to talk about the same “pagaduria” you have to go to the fields C26IDPAGCONSOL and C26SUCURSALCONSOL which together will make a unique key that groups the others. This is given that in the main query we can be talking about the code 550-14 and in the subquery 550-16. If they are evaluated with C26IDPAGADURIA and C26CODSUCURSAL to exclude themselves, this may be an error and that is why it must be possible to do it with C26IDPAGCONSOL and C26SUCURSALCONSOL, which is the code grouper of the “pagaduria”

The problem, as I indicated at the beginning, is that I can’t achieve this. It is worth mentioning that in “T70OBLIGACION” you have the data C70PAGADURIA and C70CODSUCURSAL, which correspond to C26IDPAGADURIA and C26CODSUCURSAL but not to C26IDPAGCONSOL and C26SUCURSALCONSOL. That is why they have been handled in the rest of the code the way you see.

I appreciate the help you can give me