Create concatenated string from a result set using SQL

[Rudolf Bargholz]

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"
A simple recursion example I have been using to test with is

with RECURSE
(SEQ , COUNTER) as
(
values(1, 20 )
union all
select
pre.SEQ + 1 , pre.COUNTER + 1
from
RECURSE pre
where COUNTER < 25
)
select
SEQ,COUNTER
from
RECURSE

but I am just not smart enough to work out how to flatten the result.

Anyone know if this type of result is possible using SQL, or will I have to
continue assigning the PaxString in my client side code? Stored procedures
are not an option, unfortunately, so I am limited to a method using SQL.

DB2 UDB v7.2 FP 12 on Windows XP
Regards

Rudolf Bargholz

[Knut Stolze]

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

[Rudolf Bargholz]

Hi Knut

....

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
No. Each order item is a copy of an item in the inventory. The inventory is
a seperte table. This allows us to change the description of the order
items, however still have a reference to the original inventory.

When printing a voucher for a hotel room, I need to know which pax are
associated with the current room. Our system even goes so far as to
associate specific price lines with specific pax. The information is
necessary when transferring order information between back office systems.
(2) you store the item-order information redundant in the PAX and in the
ITEM table - why?
Our application is used by travel agents in Switzerland. They have items in
inventory tables and also import items from external booking systems. Most
invoices and itineraries are edited by the travel agent before printing the
final document, thus we copy the inventory descriptions into the order item
tables. As the external booking systems are not part of our inventory, we
also need to copy the information from the external booking info file into
our system.
(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?
Such a small niche market, but very complex. Travel agent/tour operating
systems normally create an order that can contain one or more pax
(passengers). An example is one order containing numerous family members.
Only one invoice is necessary here. You can however have cases in which a
single invoice needs to be split up between the associated pax. Here an
association between pax and order item as well as pax and item price is
essential. Other cases occur where a family of four book two double rooms,
pax 1 and 2 are connected to the first room, pax 3 and 4 with the other.
This information is necesary in order to print the hotel vouchers for both
rooms appropriately.

Well, despite those unclear things, I'll give it a try...

WITH

....

Thank you for your help.

Regards

Rudolf Bargholz

[Knut Stolze]

Rudolf Bargholz wrote:

Hi Knut

...

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

No. Each order item is a copy of an item in the inventory. The inventory
is a seperte table. This allows us to change the description of the order
items, however still have a reference to the original inventory.

When printing a voucher for a hotel room, I need to know which pax are
associated with the current room. Our system even goes so far as to
associate specific price lines with specific pax. The information is
necessary when transferring order information between back office systems.

(2) you store the item-order information redundant in the PAX and in the
ITEM table - why?

Our application is used by travel agents in Switzerland. They have items
in inventory tables and also import items from external booking systems.
Most invoices and itineraries are edited by the travel agent before
printing the final document, thus we copy the inventory descriptions into
the order item tables. As the external booking systems are not part of our
inventory, we also need to copy the information from the external booking
info file into our system.

(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?

Such a small niche market, but very complex. Travel agent/tour operating
systems normally create an order that can contain one or more pax
(passengers). An example is one order containing numerous family members.
Only one invoice is necessary here. You can however have cases in which a
single invoice needs to be split up between the associated pax. Here an
association between pax and order item as well as pax and item price is
essential. Other cases occur where a family of four book two double rooms,
pax 1 and 2 are connected to the first room, pax 3 and 4 with the other.
This information is necesary in order to print the hotel vouchers for both
rooms appropriately.

Thanks for the explanations. Now things are much clearer to me and I
understand the table design much better. ;-)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Giganews]

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

[Serge Rielau]

DB2 recognizes simple patterns in predicates which ensure finite
recursion. The safest is a strictly monotonic integer value with a limit:

WITH threeaplusone(lvl, a)
AS (VALUES (1, CAST(12345 AS BIGINT))
UNION ALL
SELECT lvl + 1, CASE WHEN (a / 2) * 2 = a
THEN a / 2
ELSE 3 * a + 1 END
FROM threeaplusone
WHERE lvl < 10000 AND a <> 1)
SELECT max(lvl) FROM threeaplusone;

DB2 knows that when you add 1 to a numeric type it will eventually
exceed any number....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Rhino]

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