Estou tentando executar esse SQL:
“Select a.pex_empcod As Empresa, a.pex_nro As Numero, d.ntv_nfecha As Chave
From pexcad a
inner Join CMLCAD b On a.pex_empcod = b.cml_empcod And a.pex_nro = b.cml_nro
inner Join ntvcad d On d.ntv_empcod = a.pex_ntvemp And d.ntv_serie = a.pex_ntvser And d.ntv_numero = a.pex_ntvnum And d.cli_cod = a.pex_ntvcli
Where a.pex_empcod = 1
And a.pex_datemi between (sysdate -30) And sysdate
And (Not exists(Select a., a.rowid From cmlrex c Where c.cml_empcod = b.cml_empcod And c.cml_nro = b.cml_nro And c.cml_tipo = ‘DUE’ and c.cml_sta = ‘A’ and (not c.cml_busca is null or c.cml_busca <> ‘01/01/0001’)) or
exists(Select a., a.rowid From cmlrex c Where c.cml_empcod = b.cml_empcod And c.cml_nro = b.cml_nro And c.cml_tipo = ‘DUE’ and c.cml_sta = ‘A’ and (not c.cml_busca is null or c.cml_busca <> ‘01/01/0001’)
And (c.cml_datavb Is null Or c.cml_datavb = ‘01/01/0001’)))
Order By d.ntv_datctb”
Porem estou com erro no Sysdate - 30, ja tentei controlar por fora e dentro do sql jogar somente as variaveis porem sem sucesso, alguma dica do que pode ser?
Select a.pex_empcod As Empresa, a.pex_nro As Numero, d.ntv_nfecha As Chave
From pexcad a
inner Join CMLCAD b On a.pex_empcod = b.cml_empcod And a.pex_nro = b.cml_nro
inner Join ntvcad d On d.ntv_empcod = a.pex_ntvemp And d.ntv_serie = a.pex_ntvser And d.ntv_numero = a.pex_ntvnum And d.cli_cod = a.pex_ntvcli
Where a.pex_empcod = 1
And a.pex_datemi between (sysdate - interval '30' day) And sysdate
And (Not exists(Select a.*, a.rowid From cmlrex c Where c.cml_empcod = b.cml_empcod And c.cml_nro = b.cml_nro And c.cml_tipo = 'DUE' and c.cml_sta = 'A' and (not c.cml_busca is null or c.cml_busca <> '01/01/0001')) or
exists(Select a.*, a.rowid From cmlrex c Where c.cml_empcod = b.cml_empcod And c.cml_nro = b.cml_nro And c.cml_tipo = 'DUE' and c.cml_sta = 'A' and (not c.cml_busca is null or c.cml_busca <> '01/01/0001')
And (c.cml_datavb Is null Or c.cml_datavb = '01/01/0001')))
Order By d.ntv_datctb;