Supress SQL0347 (CTE may contain infite loop)

[w.l.fischer]

Hello,

we have a perfectly working recursive CTE which gives us SLQ0347 (The
recursive common table expression "<name>" may contain an infinite
loop.)

The CTE parses a string ("This,is,a,string"), removes word by word and
runs while length(col) 0. DB2 doesn't detect this as a terminating
condition and therefore issues the warning. Does someone have a nice
solution to work around this?

Or better yet, does someone have a function
comma_separated_string_to_table()? Something like

$ db2 "select * from table(csstt('This,is,a,string')) as a"
1
---------------
This
is
a
string

4 row(s) selected.
$

[Serge Rielau]

w.*********@googlemail.com wrote:

Hello,

we have a perfectly working recursive CTE which gives us SLQ0347 (The
recursive common table expression "<name>" may contain an infinite
loop.)

The CTE parses a string ("This,is,a,string"), removes word by word and
runs while length(col) 0. DB2 doesn't detect this as a terminating
condition and therefore issues the warning. Does someone have a nice
solution to work around this?

Just add a little counter:
WITH rec(...., level) AS (VALUES (..... 1) UNION ALL (SELECT ... level +
1 FROM ,,, WHERE level < 10000000) ...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab