DB2子查询中的CTE [英] CTE in DB2 subquery

问题描述

我试图在DB2 luw 11.1的from子句中使用递归CTE.此CTE本身是有效的:

I'm trying to use a recursive CTE in the from clause in DB2 luw 11.1. This CTE works by itself:

with i (i) as ( 
    values (1)
    union all 
    select i + 1 from i
    where i < 3 
) 
select * from i;

            I
-------------
            1
            2
            3

但是当我在 from 子句中尝试时:

But when I try it in the from clause:

select * 
from (
    with i (i) as ( 
        values (1)
        union all 
        select i + 1 from i
        where i < 3 
    )
    select * from i
) i;
ERRO próximo da linha 1:
SQL0104N  An unexpected token "as" was found following "*
from (
with i (i)".  Expected tokens may include:  "JOIN".

在Postgresql中也可以使用类似的构造.我想念什么?

A similar construct works in Postgresql. What am I missing?

推荐答案

在DB2查询中必须首先使用"with"语句，请尝试

Hi "with" statement must be first in db2 query ,try this

with i (i) as (
    values (1)
    union all
    select i + 1 from i
    where i < 3
)
select *
   from (

           select * from i
         ) i;

这篇关于DB2子查询中的CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！