DB2 -SQL 0347 W递归公用表表达式\“IINYGBKY.TBNEW\”可能包含无限循环,SQLSTATE=01605

drnojrws  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(329)
with tbNew(sts) as (
    select 
      s
    FROM 
      (values(timestamp('${startDate}','00:00:00'))) t(s)
    union all 
    select 
      sts + ${period} SECONDS
    FROM 
    tbNew 
    WHERE 
      sts + ${period} SECONDS < timestamp('${endDate}','23:59:59'))
  ) 
  select 
    sts AS dummy_interval
  FROM 
    tbNew

以上查询正在dbeaver中运行find,但在代码中引发错误:
递归公用表表达式“IINYGBKY.TBNEW”可能包含无限循环。SQLSTATE=01605
不知道我做错了什么。

6vl6ewon

6vl6ewon1#

您可以将代码调整为类似以下的内容:

with tbNew(sts, n) as (
    select 
      s, 0
    FROM 
      (values(timestamp('${startDate}','00:00:00'))) t(s)
    union all 
    select 
      sts + ${period} SECONDS, n+1
    FROM 
    tbNew 
    WHERE 
      sts + ${period} SECONDS < timestamp('${endDate}','23:59:59'))
      AND n<100000
  ) 
  select 
    sts AS dummy_interval
  FROM 
    tbNew

您可以将基本情况简化为:

with tbNew(sts, n) as (
    (values(timestamp('${startDate}','00:00:00')), 0)
    union all 
    ...

相关问题