HappyDev HappyDev - 6 months ago 70
SQL Question

How to use Recursive query in db2

I am learning to use recursive query in db2, got a problem online to print following pattern

*
**
***
****
*****
******
*******
********
*********
**********
***********


upto 20 level, solved it in Oracle using following query

select lpad('*', level, '*') result from dual connect by level <= 20


but got no idea how to do it in db2, would really appreciate any help.

Also how to do it in reverse order, means how to print stars pyramid from 20th to 1st level?

Answer

Correct query for db2, using REPEAT instead of LPAD

with x(id,val) as 
(
 select 1, REPEAT('*',1) from sysibm.sysdummy1
 union all
 select id+1, REPEAT('*',id+1) from x where id < 20
)
select val from x

 

with x(id,val) as 
(
 select 20, REPEAT('*',20) from sysibm.sysdummy1
 union all
 select id-1, REPEAT('*',id-1) from x where id > 1
)
select val from x