错误:ORA-01790:表达式必须具有与相应表达式相同的数据类型 [英] Error: ORA-01790: expression must have same datatype as corresponding expression

问题描述

运行以下内容时出现错误Error: ORA-01790: expression must have same datatype as corresponding expression

Running the following i got error Error: ORA-01790: expression must have same datatype as corresponding expression

with x (id, dateN) as
(
select 1, to_date('2015-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') from dual
union all
select id+1, dateN+1 from x where id < 10
)
select * from x

我尝试了不同的转换，例如to_char，时间戳，+时间间隔"1"天等等，但是此错误不断出现.在Mssql上，通过功能dateadd('dd', 1, dateN)非常容易，但是在这里实现该功能不是很明显.

I've tried different casts like to_char, as timestamp, + interval '1' day and so on but this error keeps on appearing. On Mssql it's very easy via function dateadd('dd', 1, dateN) but here that's not very obvious how to accomplish that.

Oracle Database 11g企业版11.2.0.1.0版-64位生产

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

推荐答案

由于您使用的是基本发行版，因此看起来像11840579.您可以通过强制转换值来解决它-不必这样做，但这是您的错误:

Since you're on the base release this looks like but 11840579. You may be able to work around it by casting the value - it shouldn't be necessary, but that's bugs for you:

with x (id, dateN) as
(
  select 1, cast(to_date('2015-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') as date) from dual
  union all
  select id+1, dateN+1 from x where id < 10
)
select * from x;

在转换中包括多余的元素是没有意义的；我个人还是喜欢日期文字:

Including extra elements in the conversion is a bit pointless; personally I prefer date literals anyway:

with x (id, dateN) as
(
  select 1, cast(date '2015-05-01' as date) from dual
  union all
  select id+1, dateN+1 from x where id < 10
)
select * from x;

date '2015-01-01'和cast(date '2015-05-01' as date)这两个值是略有不同的类型，具有不同的内部表示形式，这似乎是导致问题的原因:

The two values, date '2015-01-01' and cast(date '2015-05-01' as date) are slightly different types, with different internal representations, which seems to be causing the problem:

select dump(date '2015-05-01', 16) as d1, dump(cast(date '2015-05-01' as date), 16) as d2
from dual;

D1                               D2                             
-------------------------------- --------------------------------
Typ=13 Len=8: df,7,5,1,0,0,0,0   Typ=12 Len=7: 78,73,5,1,1,1,1   

但是，该错误还有另外一部分，那就是它可以返回错误的结果.如果您无法修补以避免出现此问题，则可以使用较旧的分层查询方法:

However there's a second part to the bug which is that it can return the wrong results. If you can't patch up to avoid the problem, you could use the older hierarchical-query approach:

select level as id, date '2015-05-01' + level - 1 as dateN
from dual
connect by level < 10;

这篇关于错误:ORA-01790:表达式必须具有与相应表达式相同的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！