ORA-00932: inconsistent data types: expected CHAR got NUMBER when adding 1 to date
Probably a stupid mistake, but I couldn't figure it out myself. When I run this query in Oracle 11g.
If this question was answered by SO please give me a link.
with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D'),
, '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
, '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
, to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
HOLIDAYS as (select distinct rpt_day
from rpt_days rpt left join
calendars cal on rpt.calendar_id = cal.calendar_id
where rpt.type = 2
and cal.group = 4)
select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
then LAST_BD
else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
, '6', LAST_BD
, '2', LAST_BD
, LAST_BD)
end as LAST_BD_OF_MONTH
from LAST_BUSINESS_DAY LBD
inner join HOLIDAYS H on LBD.LAST_BD = H.rpt_day
I am getting the result as
LAST_BD_OF_MONTH =================== 29-MAR-2013
Now when I try to add a day to a date LAST_BD
it throws an error.
with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D'),
, '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
, '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
, to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
HOLIDAYS as (select distinct rpt_day
from rpt_days rpt left join
calendars cal on rpt.calendar_id = cal.calendar_id
where rpt.type = 2
and cal.group = 4)
select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
then LAST_BD
else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D') -- line 35
, '6', LAST_BD - 1 -- CHANGED THIS
, '2', LAST_BD + 1 -- CHANGED THIS
, LAST_BD)
end as LAST_BD_OF_MONTH
from LAST_BUSINESS_DAY LBD
inner join HOLIDAYS H on LBD.LAST_BD = H.rpt_day
Error message
ORA-00932: inconsistent data types: expected CHAR received NUMBER
00932. 00000 - "inconsistent data types: expected% s received% s" * Reason:
* Action: Error at line: 35 Column: 20
As I said, this can be a simple view from my side. I tried converting LAST_BD
to date
but didn't work.
I tried to change DECODE
as shown below
case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
then LAST_BD
else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
, '6', to_date(LAST_BD, 'DD-MON-YYYY') - 1
, '2', LAST_BD + 1 -- line 37
, LAST_BD)
end as LAST_BD_OF_MONTH
and got this error:
ORA-00932 inconsistent data types: expected DATE received NUMBER
00932.00000 - "inconsistent data types: expected% s received% s" * Reason:
* Action: Error at line: 37 Column: 42
So I changed line 37
to this,
case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
then LAST_BD
else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
, '6', to_date(LAST_BD, 'DD-MON-YYYY') - 1
, '2', to_date(LAST_BD, 'DD-MON-YYYY') + 1
, LAST_BD)
end as LAST_BD_OF_MONTH
and this time it's a different message.
ORA-00932: inconsistent data types: expected CHAR received DATE
00932. 00000 - "inconsistent data types: expected% s received% s" * Reason:
* Action: Error at line: 35 Column: 20
Any help to get this fix is greatly appreciated.
ANSWER :
with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D'),
, '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
, '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
, to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
HOLIDAYS as (select distinct rpt_day
from rpt_days rpt left join
calendars cal on rpt.calendar_id = cal.calendar_id
where rpt.type = 2
and cal.group = 4)
select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
then LAST_BD
else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
, '6', to_char (to_date(LAST_BD, 'DD-MON-YYYY') - 1, 'DD-MON-YYYY')
, '2', to_char (to_date(LAST_BD, 'DD-MON-YYYY') + 1, 'DD-MON-YYYY')
, LAST_BD)
end as LAST_BD_OF_MONTH
from LAST_BUSINESS_DAY LBD
inner join HOLIDAYS H on LBD.LAST_BD = H.rpt_day
source to share
So, I understand correctly that you converted LAST_BD
from VARCHAR2
to DATE
(because of):
to_date(LAST_BD, 'DD-MON-YYYY')
In your second request, you are trying to subtract 1
from this VARCHAR2
:
LAST_BD - 1
It won't work. As a consequence, you receive an error message:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
Which will probably work if you convert it to DATE
, add 1
and convert it back toVARCHAR2
with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D'),
, '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
, '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
, to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
HOLIDAYS as (select distinct rpt_day
from rpt_days rpt left join
calendars cal on rpt.calendar_id = cal.calendar_id
where rpt.type = 2
and cal.group = 4)
select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
then LAST_BD
else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D') -- line 35
, '6', to_char (to_date(LAST_BD, 'DD-MON-YYYY') - 1, 'DD-MON-YYYY')
, '2', to_char (to_date(LAST_BD, 'DD-MON-YYYY') + 1, 'DD-MON-YYYY')
, LAST_BD)
end as LAST_BD_OF_MONTH
from LAST_BUSINESS_DAY LBD
inner join HOLIDAYS H on LBD.LAST_BD = H.rpt_day
Note that conversion back to is required VARCHAR2
because DECODE
only values of one type are allowed.
source to share
The problem is that DECODE expects a CHAR argument of some type, LAST_BD + 1
and even TO_DATE(LAST_BD...
returns NUMBER and DATE, respectively.
The following SQL script demonstrates how to fix this.
http://sqlfiddle.com/#!4/8ac4a3/9
Here is the request:
with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D')
, '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
, '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
, to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
HOLIDAYS as (select distinct reporting_day
from tbm_reporting_days trdy left join
tbm_calendars tcal on trdy.calendar_id = tcal.calendar_id
where trdy.type = 2
and tcal.site_id = 4)
select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
then LAST_BD
else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
, '6', TO_CHAR(
(TO_DATE(LAST_BD, 'DD-MON-YYYY') - 1), 'D')
, '2', TO_CHAR(
(TO_DATE(LAST_BD, 'DD-MON-YYYY') - 1), 'D')
, LAST_BD)
end as LAST_BD_OF_MONTH
from LAST_BUSINESS_DAY LBD
inner join HOLIDAYS H on LBD.LAST_BD = H.reporting_day
You need to convert number or date back to CHAR with TO_CHAR.
source to share