alt text 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

asked 07 Oct '13, 10:15

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%


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
permanent link

answered 07 Oct '13, 10:54

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

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

permanent link

answered 07 Oct '13, 11:12

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 07 Oct '13, 11:13

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18

question asked: 07 Oct '13, 10:15

question was seen: 3,635 times

last updated: 07 Oct '13, 11:13