sql - ORA-00932 : inconsistent datatypes: expected CHAR got NUMBER while adding 1 to a date

Question

可能是个愚蠢的错误，但我自己无法弄清楚。当我在 Oracle 11g 中运行此查询时。

如果在 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

我得到的结果是

LAST_BD_OF_MONTH
===================
29-MAR-2013

现在，当我尝试向 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

错误信息

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，但没有成功。

我试着改变 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: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 37 Column: 42

因此，我将 第 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

这次是不同的消息。

ORA-00932: inconsistent datatypes: expected CHAR got DATE
00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action: Error at Line: 35 Column: 20

非常感谢任何帮助更正此问题的帮助。

回答:

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

Answer 1

那么我看对了吗，您将 LAST_BD 从 VARCHAR2 转换为 DATE(由于):

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

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

LAST_BD - 1

这行不通。结果你得到错误:

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

可能可行的是，如果将其转换为 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

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