ORA-00932:不一致的数据类型:预期CHAR在添加1到日期时得到了数字。

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_BD date, 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:不一致的数据类型:预期字符数为00932。00000 -“不一致的数据类型:期望%s得到%s”*原因:*操作:错误在线:35列:20。

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.

就像我说的，这可能只是我的一个简单的疏忽。我尝试将LAST_BD转换为一个日期，但没有成功。

I tried changing the DECODE as 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 datatypes: expected DATE got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 37 Column: 42

ORA-00932:不一致的数据类型:预期日期为00932。00000 -“不一致的数据类型:预期%s有%s”*原因:*操作:错误在线:37栏:42。

So, I changed the line 37 to this,

我把这条线改成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得到的日期是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

3 个解决方案

#1

So do I see it right, that you converted LAST_BDfrom VARCHAR2 to DATE (due to):

我看对了吗，你把LAST_BDfrom VARCHAR2转换成DATE(因为)

to_date(LAST_BD, 'DD-MON-YYYY')

In the second query you try to subtract 1 from this VARCHAR2:

在第二个查询中，您试图从这个VARCHAR2中减去1:

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 1 and 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 VARCHAR2 is required, because DECODE allows only values of one type.

注意，需要转换回VARCHAR2，因为DECODE只允许一个类型的值。

#2

The issue is that DECODE expects a CHAR argument of some kind and LAST_BD + 1 and even the TO_DATE(LAST_BD... return a NUMBER and a DATE respectively.

问题是DECODE期望得到某种类型的CHAR参数、LAST_BD + 1甚至TO_DATE(LAST_BD……)分别返回一个数字和一个日期。

The following SQL Fiddle demonstrate how to fix this.

下面的SQL小提琴演示了如何修复这个问题。

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的字符。

#3

It seems Oracle requires for case structures the type of all then expressions to be consistent with the type of the expression after the first then.
No type conversions are performed.

似乎Oracle需要用例结构，然后所有表达式的类型与第一个之后的表达式的类型一致。不执行类型转换。

#1