The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
86404550
accept rate: 10%


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.7k171174
accept rate: 24%

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
29.3k287438644
accept rate: 32%

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:

×17

question asked: 07 Oct '13, 10:15

question was seen: 1,136 times

last updated: 07 Oct '13, 11:13