Too many recursive iteration times.

[ximen]

An error occurred when reading the results of the SQL statement Shows the results may be incorrect or incomplete Too many recursive iteration times SQLCODE=-923，ODBC 3 status="HY000"

with recursive ps(product_code,description,parent_id) as
(select product_code,description,parent_id from product_code where parent_id='cp'
union all
select b.product_code,b.product_code,ps.parent_id from ps,product_code b  where ps.parent_id=b.product_code)
select * from ps

[jeff_albion]

See: SQLCODE -923 "Too many recursive iterations". You have exceeded the maximum recursion level of the server, set by max_recursive_iterations.

You have numerous errors in your recursive SQL query:

1. Your SQL recursive statement doesn't select the same columns as your first base SQL statement.
2. Your WHERE clause on your recursive query is reversed - you need to join: b.parent_id = ps.product_code
3. Your first SQL query is looking to match rows based on parent_id = 'cp' - while you can certainly do this, I would suspect you are really trying to use where product_code = 'cp'.

Here is the SQL:

with recursive ps(product_code,description,parent_id) as
(select product_code,description,parent_id from product_code where product_code='cp'
union all
select b.product_code,b.description,b.parent_id from ps,product_code b  where
ps.product_code=b.parent_id)
select * from ps

[VolkerBarth]

To add to Jeff's explanation:

Furthermore it might not be clear that the recursive query (i.e. the "select b.product_code, ... where ps.product_code=b.parent_id") has a limitation itself - the error given usually does come up when the recursion enters a loop, that is when the same rows will be added again and again...

IMHO, that will happen here as there is no WHERE clause that prevents another joined row from ps x b to be added recursively to ps...