Translation SQL script for Oracle

3

I have the script below in SQL Server, but I need to mount it in Oracle. I do not know anything about Oracle and I could not make it work. can you help me? Oracle: 11.2.0.4.0

SQL Script:

DECLARE @DataExec datetime
DECLARE @DataRef datetime

SET @DataExec = '20161008'
set @DataRef = '20101205'

Select datediff(dd, @DataExec, Max(DatBase)) as DiasDiferenca,  
    max(DatBase) as DataLimite,
    count(1) as Qtde_Registros
from Tabela
where datbase <=@DataRef

Oracle Script Attempt:

VAR DataExec date
VAR DataRef date

Exec :DataExec := to_date('20161008','YYYYMMDD')
Exec :DataRef := to_date('20101205','YYYYMMDD')

Select (:DataExec - max(DATBASE)) as DiasDiferenca,  
    max(DATBASE) as DataLimite,
    count(1) as Qtde_Registros
from DELQMST 
where DATBASE <= :DataRef;

Error:

  

ORA-00932: inconsistent datatypes: expected CHAR got DATE

    
asked by anonymous 10.10.2016 / 16:06

2 answers

0

Try:

v_DataExec timestamp(3) v_DataRef timestamp(3)

v_DataExec := '20161008' v_DataRef := '20101205'

Select Max - v_DataExec as DiasDiferenca,
max(DatBase) as DataLimite, count(1) as Qtde_Registros from Tabela where datbase <=v_DataRef
    
10.10.2016 / 16:12
0

Try this way:

SELECT (TO_DATE('20161008', 'YYYYMMDD') - MAX(DatBase)) AS diasdiferenca
      ,MAX(DatBase) AS datalimite
      ,COUNT(1) AS qtde_registros
  FROM DELQMST
 WHERE datbase <= TO_DATE('20101205', 'YYYYMMDD')
    
10.10.2016 / 16:16