CTE in DB2 subquery

Question

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?

Why do you need a CTE in the FROM clause? Looks like an X-Y problem to me. As you can see, it's syntactically incorrect in DB2 (and semantically unnecessary). — mustaccio
– mustaccio, Commented Jun 2, 2017 at 20:18
@mustaccio It could also be a correlated subquery in the select list but I thought it would be cleaner in a lateral join. I can not test the correlated subquery now. Is it legal? — Clodoaldo Neto
– Clodoaldo Neto, Commented Jun 2, 2017 at 21:36

zlidime · Accepted Answer · 2017-06-02 20:21:08Z

2

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;

answered Jun 2, 2017 at 20:21

zlidime

1,23412 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukas Eder · Accepted Answer · 2021-03-09 17:47:32Z

1

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

answered Mar 9, 2021 at 17:47

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

2 Comments

Clodoaldo Neto Over a year ago

Thank you for the update. I still don't have access to 11.5 to test it.

Lukas Eder Over a year ago

@ClodoaldoNeto: The current version on docker hub is 11.5: hub.docker.com/r/ibmcom/db2

Collectives™ on Stack Overflow

CTE in DB2 subquery

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related