CODEWITHSUNDEEP
recursiondb2common-table-expressiondb2-luw
Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

CTE in DB2 subquery

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

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".

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

Upvotes: 1

Views: 1278

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220932

For the record, your query works just fine in DB2 v11.5.4.0:

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

producing the expected:

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

But it didn't work yet in version DB2 v11.1.4.4 as can be seen in this db fiddle

Upvotes: 1

zlidime
zlidime

Reputation: 1224

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;

Upvotes: 2

Related Questions