递归SQL给出ORA-01790 [英] Recursive SQL giving ORA-01790
问题描述
使用Oracle 11g第2版,以下查询给出ORA-01790:表达式必须与相应的表达式具有相同的数据类型:
Using Oracle 11g release 2, the following query gives an ORA-01790: expression must have same datatype as corresponding expression:
with intervals(time_interval) AS
(select trunc(systimestamp)
from dual
union all
select (time_interval + numtodsinterval(10, 'Minute'))
from intervals
where time_interval < systimestamp)
select time_interval from intervals;
该错误表明UNION ALL的两个子查询的数据类型返回不同的数据类型.
The error suggests that the datatype of both subqueries of the UNION ALL are returning different datatypes.
即使我在每个子查询中都将其转换为TIMESTAMP,也将得到相同的错误.
Even if I cast to TIMESTAMP in each of the subqueries, then I get the same error.
我想念什么?
编辑:我不是在寻找CONNECT BY替代产品.
I'm not looking for a CONNECT BY replacement.
推荐答案
在我看来,对于带有日期或时间戳记列的查询,递归子查询分解"在11g R2中已被破坏.
In my opinion, "Recursive Subquery Factoring" is broken in 11g R2 for queries with date or timestamp column.
with test(X) as
(
select to_date('2010-01-01','YYYY-MM-DD') from dual
union all (
select (X + 1) from test where X <= to_date('2010-01-10','YYYY-MM-DD')
)
)
select * from test;
ORA-01790
使用强制转换来转换数据类型:
use a cast to convert the datatype:
with test(X) as
(
select cast(to_date('2010-01-01','YYYY-MM-DD') as date) from dual
union all (
select (X + 1) from test where X <= to_date('2010-01-10','YYYY-MM-DD')
)
)
select * from test;
X
-------------------
2010-01-01 00:00:00
1 row selected
在日期中添加日期会有所帮助,但是其他结果在哪里呢?
Casting a date into a date is helping, but where are the other results?
它变得更好...
尝试另一个开始日期:
with test(X) as
(
select cast(to_date('2007-01-01','YYYY-MM-DD') as DATE) from dual
union all (
select (X + 1) from test where X <= to_date('2011-01-11','YYYY-MM-DD')
)
)
select * from test
where rownum < 10; -- important!
X
-------------------
2007-01-01 00:00:00
2006-12-31 00:00:00
2006-12-30 00:00:00
2006-12-29 00:00:00
2006-12-28 00:00:00
2006-12-27 00:00:00
2006-12-26 00:00:00
2006-12-25 00:00:00
2006-12-24 00:00:00
9 rows selected
倒数?为什么?
2014年1月14日更新:作为一种解决方法,请使用从结束日期开始的CTE,并向后构建递归CTE,如下所示:
Update 14-Jan-2014: As a workaround, use the CTE starting with the end date and building the recursive CTE backwards, like this:
with test(X) as
(
select cast(to_date('2011-01-20','YYYY-MM-DD') as DATE) as x from dual
union all (
select cast(X - 1 AS DATE) from test
where X > to_date('2011-01-01','YYYY-MM-DD')
)
)
select * from test
结果:
| X |
|--------------------------------|
| January, 20 2011 00:00:00+0000 |
| January, 19 2011 00:00:00+0000 |
| January, 18 2011 00:00:00+0000 |
| January, 17 2011 00:00:00+0000 |
| January, 16 2011 00:00:00+0000 |
| January, 15 2011 00:00:00+0000 |
| January, 14 2011 00:00:00+0000 |
| January, 13 2011 00:00:00+0000 |
| January, 12 2011 00:00:00+0000 |
| January, 11 2011 00:00:00+0000 |
| January, 10 2011 00:00:00+0000 |
| January, 09 2011 00:00:00+0000 |
| January, 08 2011 00:00:00+0000 |
| January, 07 2011 00:00:00+0000 |
| January, 06 2011 00:00:00+0000 |
| January, 05 2011 00:00:00+0000 |
| January, 04 2011 00:00:00+0000 |
| January, 03 2011 00:00:00+0000 |
| January, 02 2011 00:00:00+0000 |
| January, 01 2011 00:00:00+0000 |
进行以下测试:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
这篇关于递归SQL给出ORA-01790的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!