RE: SQL Recursive Common Table Experssion

Ok, then you'll also need to incorporate the Row_Number OLAP function into your script as well.

-- Intermediate file created from original file with service code ranking integer output:

create table qtemp.serial_master_ranked as (Selectorder#, line#, serial#,row_number() over(partition by order# order by order#, serial#) as rnkFromqtemp.serial_master) With Data;

-- Now use the field rnk to join records recursively.

with recursive serial_masters (order#, serial#s, line#, rnk) as (selectorder#, cast(serial# as varchar(96)), line#, rnkFromqtemp.serial_master_ranked where rnk = 1union allselecta.order#, b.serial#s concat ':' concat a.serial#, a.line#, b.rnk+1Fromqtemp.serial_master_ranked as aJoinserial_masters as bOn a.order# = b.order#and b.rnk+1 = a.rnk)selectorder#,line#,serial#sFromserial_masters as awherernk = (select max(b.rnk) from serial_masters as b where a.order# = b.order#)order byorder#, line#;

Date: Tue, 30 Sep 2014 14:59:00 -0400
Subject: Re: SQL Recursive Common Table Experssion
From: glenn.gundermann@xxxxxxxxx
To: midrange-l@xxxxxxxxxxxx

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