递归SQL给出ORA-01790
递归SQL给出ORA-01790
提问于 2010-04-06 16:50:48
使用Oracle11g版本2，以下查询提供了一个ORA-01790:表达式必须具有与相应表达式相同的数据类型：
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的两个子查询的数据类型都返回不同的数据类型。
即使我在每个子查询中转换为时间戳，也会得到相同的错误。
我遗漏了什么？
编辑：，我不想通过替换来寻找连接。
回答 3
Stack Overflow用户
回答已采纳
发布于 2010-05-17 17:36:54
在我看来，对于带有日期或时间戳列的查询，“递归子查询保理”在11g R2中中断。
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使用强制转换来转换数据类型：
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把约会变成约会是有帮助的，但是其他的结果在哪里呢？
会变得更好..。
用另一个开始日期试试：
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倒计时？为什么？
Update14-2014年1月：作为一种解决方法，从结束日期开始使用并向后构建递归CTE，如下所示：
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 ProductionStack Overflow用户
发布于 2011-09-07 21:52:00
奇数-如果您传递varchars并转换(而不是强制转换)：
WITH intervals(time_interval) AS
(SELECT to_char(TRUNC(systimestamp))
FROM dual
UNION ALL
SELECT to_char(to_timestamp(time_interval) + numtodsinterval(10, 'Minute'))
FROM intervals
WHERE to_timestamp(time_interval) < systimestamp
)
SELECT to_timestamp(time_interval) time_interval
FROM intervalsStack Overflow用户
发布于 2010-04-06 19:50:18
我不知道类型不匹配，但是这里有一个替代方法来实现我认为您想要的(在10gr2中工作)：
select base_time + numtodsinterval( 10*(level-1), 'Minute')
from (select trunc(systimestamp) base_time from dual)
connect by base_time + numtodsinterval( 10*(level-1), 'Minute') < systimestamp页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2586653
复制相关文章
相似问题
腾讯云开发者
