CODEWITHSUNDEEP
sqloracle-databasedate
Dmitrii
Dmitrii

Reputation: 624

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

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 Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Upvotes: 0

Views: 4278

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

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;

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;

Upvotes: 2

Related Questions