Oracle - WHERE WITH SYSDATE

Question

In the first select the BALANCE is coming zeroed, in the second I replace the sysdate by which would be the date it brings the correct value

what must be the problem in sysdate+4 in Where?

SELECT SYSDATE+4 DATA, (SELECT NVL(SUM(E1_SALDO),0) A 
FROM PROTHEUS.SE1010
INNER JOIN PROTHEUS.SED010 ON ED_CODIGO=E1_NATUREZ
WHERE SE1010.D_E_L_E_T_<>'*' AND SED010.D_E_L_E_T_<>'*' AND E1_SALDO<>'0' AND (TO_DATE(E1_VENCREA, 'YYYYMMDD')) = (SYSDATE+4))A
FROM DUAL

SELECT SYSDATE+4 DATA, (SELECT NVL(SUM(E1_SALDO),0) A 
FROM PROTHEUS.SE1010
INNER JOIN PROTHEUS.SED010 ON ED_CODIGO=E1_NATUREZ
WHERE SE1010.D_E_L_E_T_<>'*' AND SED010.D_E_L_E_T_<>'*' AND E1_SALDO<>'0' AND E1_VENCREA = ('20190924'))A
FROM DUAL

Answer 1

The return of SYSDATE depends on how the NLS_DATE_FORMAT, the format should be different and the comparison is failing.

See an example below:

SELECT 
SYSDATE DATA1, --o retorno aqui depende do NLS_DATE_FORMAT que provavelmente retorna diferente do que está esperando 'YYYYMMDD'
TO_CHAR(SYSDATE, 'YYYYMMDD') DATA2 --Aqui estou convertendo essa data no formato que deseja, mas como char
FROM DUAL;

Below I made one more example comparing some dates:

SELECT 
     CASE TO_CHAR(DATA1, 'YYYYMMDD') WHEN DATA2 THEN 'V'
     ELSE 'F' END COMPARA_1_2,
     CASE TO_CHAR(DATA3, 'YYYYMMDD') WHEN DATA4 THEN 'V'
     ELSE 'F' END COMPARA_3_4
 FROM
  (SELECT 
  SYSDATE DATA1, 
  TO_CHAR(SYSDATE, 'YYYYMMDD') DATA2,
  SYSDATE DATA3, 
  TO_CHAR(SYSDATE+4, 'YYYYMMDD') DATA4
  FROM DUAL) QRY;

In your query would look like this:

SELECT SYSDATE+4 DATA, (SELECT NVL(SUM(E1_SALDO),0) A 
FROM PROTHEUS.SE1010
INNER JOIN PROTHEUS.SED010 ON ED_CODIGO=E1_NATUREZ
WHERE SE1010.D_E_L_E_T_<>'*' AND SED010.D_E_L_E_T_<>'*' AND E1_SALDO<>'0' AND (TO_CHAR(E1_VENCREA, 'YYYYMMDD')) = (TO_CHAR(SYSDATE+4, 'YYYYMMDD')))A
FROM DUAL