ORA-00932: 不一致的数据类型:将 1 添加到日期时预期 CHAR 得到 NUMBER [英] ORA-00932: inconsistent datatypes: expected CHAR got NUMBER while adding 1 to a date
问题描述
可能是一个愚蠢的错误，但我自己无法弄清楚.当我在 Oracle 11g 中运行此查询时.
Probably a silly mistake, but I couldn't figure this out myself. When I run this query in Oracle 11g.
如果在 SO 中回答了这个问题，请告诉我链接.
If this question is answered in SO, please let me know the 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
我得到的结果为
LAST_BD_OF_MONTH
===================
29-MAR-2013
现在，当我尝试向 LAST_BD 日期添加一天时，它会引发错误.
Now, when I try to add a day to the LAST_BD date, 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
错误信息
ORA-00932:不一致的数据类型:预期的 CHAR 得到 NUMBER
00932. 00000 - 不一致的数据类型:预期 %s 得到 %s"*原因:
*操作:错误行:35 列:20
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 35 Column: 20
正如我所说，从我的角度来看，这可能是一个简单的忽视.我尝试将 LAST_BD 转换为 date，但没有成功.
As I said, this might be a simple overlook from my side. I tried converting the LAST_BD to a date, but didn't work.
我尝试更改 DECODE 如下
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
并收到此错误:
ORA-00932:不一致的数据类型:预期 DATE 得到 NUMBER
00932. 00000 - 不一致的数据类型:预期 %s 得到 %s"*原因:
*操作:错误在行:37 列:42
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 37 Column: 42
所以，我把line 37改成了这个，
So, I changed the 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
这次是不同的信息.
ORA-00932:不一致的数据类型:预期的 CHAR 得到 DATE
00932. 00000 - 不一致的数据类型:预期 %s 得到 %s"*原因:
*操作:错误行:35 列:20
ORA-00932: inconsistent datatypes: expected CHAR got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 35 Column: 20
非常感谢您对纠正此问题的任何帮助.
Any help to get this corrected is greatly appreciated.
答案:
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
推荐答案
我没看错吧，您将 LAST_BD 从 VARCHAR2 转换为 DATE(由于):
So do I see it right, that you converted LAST_BDfrom VARCHAR2 to DATE (due to):
to_date(LAST_BD, 'DD-MON-YYYY')
在第二个查询中，您尝试从这个 VARCHAR2 中减去 1:
In the second query you try to subtract 1 from this VARCHAR2:
LAST_BD - 1
这行不通.结果你得到错误:
This won't work. As a consequence you get the error:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
可能有效的是，如果您将其转换为 DATE，添加 1 并将其转换回 VARCHAR2
What would probably work is, if you convert it to DATE, add 1 and convert it back to VARCHAR2
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
请注意，需要转换回 VARCHAR2，因为 DECODE 只允许一种类型的值.
Note that the conversion back to VARCHAR2 is required, because DECODE allows only values of one type.
这篇关于ORA-00932: 不一致的数据类型:将 1 添加到日期时预期 CHAR 得到 NUMBER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
