SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01790 from 11gR2 Recursive with clause

Aketi JyuuzouApr 7 2010 — edited Apr 8 2010

I guess below result is bug.

SQL> select * from v$version;

BANNER
--------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> with tmp(day1) as(select date '2009-06-01' from dual),
  2  rec(day1) as(
  3  select day1 from tmp
  4  union all
  5  select add_months(day1,1)
  6    from rec
  7   where add_months(day1,1) < date '2010-05-05')
  8  select * from rec;
select add_months(day1,1)
       *
行5でエラーが発生しました。:
ORA-01790: 式には対応する式と同じデータ型を持つ必要があります

I suppose above two expression is same data type,because below SQL is executable.

with tmp(day1) as(select date '2009-06-01' from dual)
select day1 from tmp
union all
select add_months(day1,1) from tmp;

day1
--------
09-06-01
09-07-01

Aketi Jyuuzou

and one more

SQL> with tmp(day1) as(select date '2009-06-01' from dual),
  2  rec(day1) as(
  3  select day1 from tmp
  4  union all
  5  select day1+1
  6    from rec
  7   where day1+1 < date '2010-05-05')
  8  select * from rec;
select day1+1
           *
行5でエラーが発生しました。:
ORA-01790: 式には対応する式と同じデータ型を持つ必要があります

009

Well strange error I got for the SQLs you have given :p

with tmp(day1) as(select date '2009-06-01' from dual),
    rec(day1) as(
    select day1 from tmp
    union all
    select add_months(day1,1)
      from rec
     where add_months(day1,1) < date '2010-05-05')
    select * from rec;

ORA-32033: unsupported column aliasing

with tmp(day1) as(select date '2009-06-01' from dual)
select day1 from tmp
union all
select add_months(day1,1) from tmp;

ORA-32033: unsupported column aliasing

SELECT *
FROM V$VERSION;

BANNER
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

*009*

Edited by: 009 on Apr 7, 2010 9:16 PM

Aketi Jyuuzou

SomeHow we have to use cast expressions like below SQL ?:|

with rec(day1) as(
select cast(date '2009-06-01' as date) from dual
union all
select cast(add_months(day1,1) as date)
  from rec
 where add_months(day1,1) < date '2009-10-05')
select * from rec;

DAY1
--------
09-06-01
09-07-01
09-08-01
09-09-01
09-10-01

But I insist that we should be able to omit above cast expressions :-(
Below SQL proves that two expressions have the same date type B-)

select dump(date '2009-06-01') as c1,
       dump(add_months(date '2009-05-01',1)) as c2 from dual;

C1                               C2
-------------------------------  -------------------------------
Typ=13 Len=8: 217,7,6,1,0,0,0,0  Typ=13 Len=8: 217,7,6,1,0,0,0,0

Aketi Jyuuzou

hi 009

your oracle version 11.1.0.7.0 is not supported recursive with clause :-)

009

I'm outdated even with 11GR1 :_|

*009*

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on May 6 2010

Added on Apr 7 2010

5 comments

3,294 views

SQL & PL/SQL

ORA-01790 from 11gR2 Recursive with clause

Comments

Post Details