Re: SQL Recursive Common Table Experssion

Hi Jim,

I appreciate your help!
There is one difference between the data you inserted and the data in
SERIAL_MASTER we have here.
If you change Line# to the same value, it would better reflect the
difficulty I am having here. I.E.
update qtemp.serial_master set line#=1;

Thank you.
Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 795-2111 ext. 3098
Cell: (416) 317-3144

On 30 September 2014 11:50, <midrange-l-request@xxxxxxxxxxxx> wrote:

message: 5
date: Tue, 30 Sep 2014 11:48:02 -0400
from: Jim It <jim_it@xxxxxxxxxxx>
subject: RE: SQL Recursive Common Table Expression

Hi Glenn,
recursion is pretty neat and very powerful. Here a sample using data like
you posted.

create table qtemp.serial_master (order#, line#, serial#) as
(values(integer(123456), integer(1), char('abc',3)),(integer(123456),
integer(2), char('def',3)),(integer(123456), integer(3), char('ghi',3)))
with data
insert into qtemp.serial_master(values(integer(234567), integer(1),
char('abc',3)),(integer(234567), integer(2),
char('def',3)),(integer(234567), integer(3), char('ghi',3)))
insert into qtemp.serial_master(values(integer(345678), integer(1),
char('jkl',3)),(integer(345678), integer(2),
char('mno',3)),(integer(345678), integer(3), char('pqr',3)))
with recursive serial_masters (order#, serial#s, line#) as (selectorder#,
cast(serial# as varchar(96)), line#fromqtemp.serial_master where line# =
1union allselecta.order#, b.serial#s concat ':' concat a.serial#,
b.line#+1fromqtemp.serial_master as ajoinserial_masters as bon a.order# =
b.order#and b.line#+1 = a.line#)selectorder#,1 as
line#,serial#sfromserial_masters as awhereline# = (select max(b.line#) from
serial_masters as b where a.order# = b.order#)order byorder#, line#
OutputORDER#LINE#SERIAL#S
1234561abc:def:ghi
2345671abc:def:ghi
3456781jkl:mno:pqr

Jim

Date: Tue, 30 Sep 2014 11:02:08 -0400
Subject: SQL Recursive Common Table Expression
From: glenn.gundermann@xxxxxxxxx
To: midrange-l@xxxxxxxxxxxx

Hi all,

I'm havnig some difficulties writing a recursive CTE to concatenate a

text

field together from multiple rows.
I'm wondering if anyone can help.

I have tables ORDER_HEADER, ORDER_DETAILS, and SERIAL_MASTER.
One header can have multiple details and one detail can have multiple
serial #s.
A single order line item can have more than one machine and hence the
serial # masterfile has multiple rows with the same order# and line

item#.

Example of data in SERIAL_MASTER:

Order# Line# Serial#
123456 1 ABC
123456 1 DEF
123456 1 GHI

I would like to produce the following:

Order# Line# Serial #s
123456 1 ABC: DEF: GHI

Any assistance would be greatly appreciated.


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 795-2111 ext. 3098
Cell: (416) 317-3144