I have below query which i am using in oracle stored procedure where i am getting error as
ORA-00932: inconsistent datatypes: expected NUMBER got DATE. day_id column is Number datatype and the data is stored in this column like for e.g 20110822 and ltds column is date datatype and value is stored in this column for e.g 08-AUG-17. Both the column stored the actual date.
In my stored procedure i am putting this query in variable like below:
NESS_QUERY:= select t.day_id from M_TIME t where TO_DATE (t.day_id, ''YYYYMMDD'') < trunc('''||LTDS||''') and TO_DATE (t.day_id, ''YYYYMMDD'') >= trunc(sysdate, ''mm'') - case trunc('''||LTDS||''') when trunc(sysdate, ''mm'') then 1 else 0 end; DBMS_OUTPUT.PUT_LINE (NESS_QUERY); The output of DBMS_OUTPUT.PUT_LINE is below:
select t.day_id from M_TIME t where TO_DATE (t.day_id, 'YYYYMMDD') < trunc('15-DEC-17') and TO_DATE (t.day_id, 'YYYYMMDD') >= trunc(sysdate, 'mm') - case trunc('15-DEC-17') when trunc(sysdate, 'mm') then 1 else 0 end 4 Answers
If you have any influence over the choice of datatype for this column, please get it changed to be of DATE datatype. That will make things (like the query you're attempting to run) much easier.
As your column is not currently DATE datatype, if you want to compare the contents of the column to a date, you need to convert the column via to_date() along with an appropriate format mask. In your case, it looks like the format mask is yyyymmdd.
So your query should be something along the lines of:
select t.day_id from m_time t where to_date(t.day_id, 'yyyymmdd') >= trunc(sysdate, 'mm') - case trunc(ltds) when trunc(sysdate, 'mm') then 1 else 0 end; below condition causing the error,
t.day_id >= trunc(sysdate, 'mm') day_id is a number, trunc(sysdate, 'mm') returns date datatype
You should convert day_id to date before comparison, using TO_DATE
SELECT t.day_id FROM M_TIME t WHERE TO_DATE (t.day_id, 'YYYYMMDD') >= TRUNC (SYSDATE, 'mm') - CASE TRUNC (ltds) WHEN TRUNC (SYSDATE, 'mm') THEN 1 ELSE 0 END; EDIT : You don't need to use the ldts variable in your query, you may save it and use it in other places, but for query you could define it and reuse from cte.
WITH l AS (SELECT TRUNC (LOAD_DATE) LDTS FROM JOB_EXE) SELECT t.day_id FROM M_TIME t CROSS JOIN l WHERE TO_DATE (t.day_id, 'YYYYMMDD') < l.LDTS AND TO_DATE (t.day_id, 'YYYYMMDD') >= TRUNC (SYSDATE, 'mm') - CASE l.LDTS WHEN TRUNC (SYSDATE, 'mm') THEN 1 ELSE 0 END where (t.day_id, 'YYYYMMDD') < trunc('31-DEC-23') and TO_DATE (t.day_id, 'YYYYMMDD') >= trunc(sysdate, 'mm') - case trunc('01-Jan-23') when trunc(sysdate, 'mm') then ('Yes') else ('No') end 
