According to some old course notes I found, "any recursive SQL statement
that does not use a control variable will receive an SQL warning
(SQL0347W)". Therefore, if you absolutely cannot tolerate getting that
warning message and simply ignoring it, you need to add a control variable.
The example from the course is rather involved and is part of a multi-page
topic. I'm really not keen on typing that much ;-) However, this is the
information from the main page that talks about this issue and I think it
probably illustrates the technique well enough without forcing you to wade
through several pages of surrounding material - and it avoids me having to
type it all!
Here is the page in question; I've capitalized the bits of the example that
are emphasized in the original graphic to get the same approximate effect;
the '0' in the initialization select should also be emphasized but I can't
think of a way to do that without confusing the syntax.
================================================== ==============
Controlling Depth of Recursion
[Example graphic:]
with rpl (LEVEL, part, subpart, quantity) as
(
select 0, root.part, root.subpart, root.quantity
from partlist root
where root.part = '00'
union all
select PARENT.LEVEL+1, child.part, child.subpart, child.quantity
from rpl parent, partlist child
where parent.subpart = child.part
and PARENT.LEVEL < 2
)
select LEVEL, part, subpart, sum(quantity) as quantity
from rpl
group by level, part, subpart
[The first Select in the example is labelled 'Initialization Select', the
second select is labelled 'Iterative Select' and the third select is
labelled 'Main Select'.]
Notes:
Recursion normally continues until all parts have been resolved into their
components. However, the depth of the recursion can be controlled by
simulating the addition of a control column to the table.
This control column is initialized arbitrarily to 0 in the initialization
select and increased by one on every execution of the iterative select. A
condition in the WHERE clause of the iterative select is used to ensure that
the iteration only continues for a fixed number of levels.
In the example, the control column is named LEVEL. It is set to an integer
value of 0 in the initialization select. The WHERE clause of the
initialization select determines the value in the PART column with which the
table expression begins. This time, we are interested in the breakdown of
Part 00, but we could have started with any part number we were interested
in. The initial value of LEVEL would still be 0, regardless of the starting
part number.
The iterative select increments the LEVEL value by adding 1 on each
iteration. The condition
PARENT.LEVEL < 2
in the WHERE clause of the iterative select is used to limit the number of
iterations: simply set the constant to the number of iterations which are
desired.
The main select displays the results of the table expression. The LEVEL
column in the final result makes the origin of each result row clear: rows
that came from the initialization select have a level of 0, rows from the
first iteration have a level of 1, rows from the second iteration have a
level of 2, and so on. The ORDER BY puts the result in a convenient
sequence.
NOTE: LEVEL is not a column of table PARTLIST. It does not have to be added
to the table PARTLIST via an ALTER TABLE statement. It is a "virtual" column
created by the SQL statement.
================================================== ==============
I hope this answers your question satisfactorily.
Rhino
"Giganews" <tdavidge@no_spam.hotmail.com> wrote in message
news:p-********************@comcast.com...
People, I know I am reposting here, but I'm really stuck on this one. I
have a similar issue as the below. I have borrowed Knut's code which works
well, but always returns the warning of a possible infinite loop. I read up as
mch as I can on recursive SQL and I cannot for the life of me find a way to
return the result without getting the warning.
I have tried limiting the result the a single value in the below PAX
table, and tried to use a max value in the PAX Item table but I still get a good
result AND the warning.
If some kind soul could PLEASE demonstrate how this could be done without
an infinite loop warning I would be most grateful.
many thanks,
Tim
"Knut Stolze" <st****@de.ibm.com> wrote in message
news:ch**********@fsuj29.rz.uni-jena.de... Rudolf Bargholz wrote:
Hi ,
I have the following tables
-------------
PAX:
Id
Order_Id
Name
Position
Id OrderId Name Position
1 1 PersonA 1
5 1 PersonB 2
19 1 PersonC 3
25 1 PersonD 4
-------------
ITEM
Id
Order_Id
Name
Id OrderId Name
1 1 Item1
2 1 Item2
-------------
PAX_ITEM
Pax_Id
Item_Id
Pax_Id Item_Id
1 1
5 1
25 1
5 2
19 2
-------------
One Item can be connected to numerous Passengers and a Passenger can
be connected to numerous Items. What I need is a list of items and the
associated Pax (Passengers) in the format:
ITEM
Id Order_Id PaxString
1 1 " 1 2 4"
2 1 " 2 3"
What you want to do is essentially an aggregation. There are some
articles that describe how you could do it with external routines, i.e. UDFs:
http://www-106.ibm.com/developerwork...309stolze.html
http://www-106.ibm.com/developerwork...lze/index.html
Now, let's get back to your problem and solve it with pure SQL. If I
got this right, then you want to know for each item the Id of the order and
also the "Position"s of the passengers, concatenated to the string. I
really don't quite understand the relations between Orders, Items and
Passengers. For example:
(1) can an item appear in multiple orders and how do you want to see
that in the output
(2) you store the item-order information redundant in the PAX and in the
ITEM table - why?
(3) I would have thought that a passenger has orders and each order has
items with positions. What's your scenario here because it is obviously
different?
Well, despite those unclear things, I'll give it a try...
WITH
-- a numbered list of all "positions" for an item-id
pax_list(item_id, order_id, row_num, pax_position) AS (
SELECT i.item_id, p.order_id,
row_number() over(PARTITION BY i.item_id ORDER BY
p.position), p.position
FROM pax_item AS i JOIN pax AS p ON
i.pax_id = p.id ),
-- recursive portion of the query to build the lists
all_lists(item_id, order_id, list, count) AS (
SELECT item_id, order_id,
VARCHAR(RTRIM(CHAR(pax_position)), 3500), 1
FROM pax_list
WHERE row_num = 1
UNION ALL
SELECT p.item_id, p.order_id,
a.list CONCAT ' ' CONCAT RTRIM(CHAR(p.pax_position)),
a.count + 1
FROM pax_list AS p, all_lists AS a
WHERE p.item_id = a.item_id AND
p.order_id = a.order_id AND
p.row_num = a.count + 1 ),
-- find the last list for each item
last_list(item_id, order_id, final_list) AS (
SELECT l.item_id, l.order_id, l.list
FROM all_lists AS l
WHERE l.count >= ALL ( SELECT m.count
FROM all_lists AS m
WHERE l.item_id = m.item_id AND
l.order_id = m.order_id ) )
SELECT item_id, order_id, final_list AS paxstring
FROM last_list
@
ITEM_ID ORDER_ID PAXSTRING
----------- ----------- ----------------------------------------
SQL0347W The recursive common table expression "STOLZE.ALL_LISTS" may
contain
an infinite loop. SQLSTATE=01605
2 1 2 3
1 1 1 2 4
2 record(s) selected with 1 warning messages printed.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
