SQL ORA-00932: 不一致的数据类型：将 1 添加到日期时预期 CHAR 得到 NUMBER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29803977/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER while adding 1 to a date
提问by Saagar Elias Hymany
Probably a silly mistake, but I couldn't figure this out myself. When I run this query in Oracle 11g.
可能是一个愚蠢的错误，但我自己无法弄清楚。当我在 Oracle 11g 中运行此查询时。
If this question is answered in SO, please let me know the link.
如果在 SO 中回答了这个问题，请告诉我链接。
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 get the result as
我得到的结果是
LAST_BD_OF_MONTH
===================
29-MAR-2013
Now, when I try to add a day to the LAST_BDdate, it throws an error.
现在，当我尝试在LAST_BD日期中添加一天时，它会引发错误。
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 datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 35 Column: 20
ORA-00932：不一致的数据类型：预期 CHAR 得到 NUMBER
00932。00000 - “不一致的数据类型：预期 %s 得到 %s”*原因：
*操作：行错误：35 列：20
As I said, this might be a simple overlook from my side. I tried converting the LAST_BDto a date, but didn't work.
正如我所说，从我的角度来看，这可能是一个简单的忽视。我尝试将 the 转换LAST_BD为 a date，但没有奏效。
I tried changing the DECODEas below
我尝试改变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
and got this error :
并收到此错误：
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 37 Column: 42
ORA-00932：不一致的数据类型：预期 DATE 得到 NUMBER
00932。00000 - “不一致的数据类型：预期 %s 得到 %s”*原因：
*操作：行错误：37 列：42
So, I changed the line 37to this,
所以，我改变了 line 37这个，
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 its a different message.
这一次是不同的信息。
ORA-00932: inconsistent datatypes: expected CHAR got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 35 Column: 20
ORA-00932：不一致的数据类型：预期的 CHAR 得到了 DATE
00932。00000 - “不一致的数据类型：预期的 %s 得到了 %s” *原因：
*操作：行错误：35 列：20
Any help to get this corrected 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
采纳答案by Trinimon
So do I see it right, that you converted LAST_BDfrom VARCHAR2to DATE(due to):
所以我看对了吗，你LAST_BD从转换VARCHAR2为DATE（由于）：
to_date(LAST_BD, 'DD-MON-YYYY')
In the second query you try to subtract 1from this VARCHAR2:
在第二个查询尝试减去1从这个VARCHAR2：
LAST_BD - 1
This won't work. As a consequence you get the error:
这行不通。结果你得到错误：
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
What would probably work is, if you convert it to DATE, add 1and convert it back to VARCHAR2
可能有效的是，如果您将其转换为DATE，添加1并转换回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
Note that the conversion back to VARCHAR2is required, because DECODEallows only values of one type.
请注意，转换回 toVARCHAR2是必需的，因为DECODE只允许一种类型的值。
回答by user2038893
It seems Oracle requires for casestructures the type of all thenexpressions to be consistent with the type of the expression after the first then.
No type conversions are performed.
Oracle 似乎要求case结构的所有then表达式的类型与第一个then之后的表达式的类型一致。
不执行类型转换。
回答by maple_shaft
The issue is that DECODE expects a CHAR argument of some kind and LAST_BD + 1and even the TO_DATE(LAST_BD...return a NUMBER and a DATE respectively.
问题是 DECODE 需要某种类型的 CHAR 参数，LAST_BD + 1甚至TO_DATE(LAST_BD...分别返回 NUMBER 和 DATE。
The following SQL Fiddle demonstrate how to fix this.
以下 SQL Fiddle 演示了如何解决此问题。
http://sqlfiddle.com/#!4/8ac4a3/9
http://sqlfiddle.com/#!4/8ac4a3/9
Here is the query:
这是查询：
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 have to convert the number or date back to a CHAR with TO_CHAR.
您必须使用 TO_CHAR 将数字或日期转换回 CHAR。
