SQL "expected DATE got NUMBER"

Question

When running the below script I get the error "ORA-00932: inconsistent datatypes: expected DATE got NUMBER". Doesn't give me a line on which the error is at. Using Oracle DB.

with reg_det as (
SELECT MCI.MEASR_COMP_ID
FROM D1_MEASR_COMP_IDENTIFIER MCI, D1_MEASR_COMP_IDENTIFIER MCR, D1_MEASR_COMP MC, D1_DVC_CFG DC, D1_DVC_IDENTIFIER DVI
WHERE MCI.MC_ID_TYPE_FLG = 'D1EI'
AND MCI.MEASR_COMP_ID = MC.MEASR_COMP_ID
AND REGEXP_SUBSTR(MCI.ID_VALUE, '[^-]+', 1, 13) = 'INT'
AND MCR.MC_ID_TYPE_FLG = 'CMRN'
AND MCR.ID_VALUE = :REG
AND MCR.MEASR_COMP_ID = MC.MEASR_COMP_ID
AND MC.DEVICE_CONFIG_ID = DC.DEVICE_CONFIG_ID
AND DVI.D1_DEVICE_ID = DC.D1_DEVICE_ID
AND DVI.DVC_ID_TYPE_FLG = 'D1SN'
AND DVI.ID_VALUE = :MTR),
gap_dates as (select to_char((:SDTTM + level -1),'YYYY-MM-DD') as read_date, 0 as interval_count, 'M' as quality, 0 as daily_load
                                  from dual
                                  connect by level <= (:EDTTM - :SDTTM) )
,read_data as (SELECT to_char(trunc(MSRMT_DTTM - 1/1440),'YYYY-MM-DD') as read_date,
count(1) as interval_count,
case
  when min(msrmt_cond_flg) > 500000 THEN 'A'
    when min(msrmt_cond_flg) > 400000 and min(msrmt_cond_flg) <= 500000 then 'F'
    when min(msrmt_cond_flg) > 300000 and min(msrmt_cond_flg) <= 400000 then 'S'
    when min(msrmt_cond_flg) > 200000 and min(msrmt_cond_flg) <= 300000 then 'E'
    when min(msrmt_cond_flg) <= 200000 then 'M' end as quality,    
sum(msrmt_val) as daily_load FROM REG_DET REG, D1_MSRMT DATA
WHERE DATA.MEASR_COMP_ID = REG.MEASR_COMP_ID
AND MSRMT_DTTM > :SDTTM
AND MSRMT_DTTM <= (:EDTTM + 1)
GROUP BY trunc(MSRMT_DTTM - 1/1440))
select * from read_data
union
select * from gap_dates a where 1=1 and not exists (select 1 from read_data b where a.read_date = b.read_date);

d r · Answer

A lot of the code provided is impossible to check as we don't know the structure of your tables nor the datatypes of the columns. I tried to cut the code in pieces to do the tests, and the only part that is returning ORA-00932 error is in the WHERE clause of the read_data cte. I'm not sure if that is your problem, but if it is it will not be the only one. What I wanted to say is that even if you correct this one there will be more errors. Let me explain - the reported error could be simulated if you bind a character value to your :EDTTM variable. Other errors would pop up if :SDTTM is of type character.
Here is the test using construction from the mentioned WHERE clause:

        WHERE 
            DATA.MEASR_COMP_ID = reg.MEASR_COMP_ID
            AND MSRMT_DTTM > :SDTTM     
            AND MSRMT_DTTM <= (:EDTTM + 1)

The last condition tested on DUAL table (assuming that MSRMT_DTTM is of type DATE) compares DATE to CHARACTER + NUMBER like in this SQL

SELECT 'anything' FROM DUAL WHERE SYSDATE <= '24-SEP-22' + 1

This results with:

/*
SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*/

If any or both of your %DTTM variables are of type character then there will be more errors (not ORA-00932) throughout your code. Additionaly, you should really consider the advice form comments to use ANSI SQL join syntax. Regards...

SQL "expected DATE got NUMBER"

1 Answers