With Recursive


With Recursive

Hi All i'm having problems writing a recursive query and i ran out of options that i can think of .

The query should join serial numbers in one cell coma delimited to a specific order number .

Sample . Service_table

Order_Num   Serial_Num
11111 ABCDE
11111 AABBC
11111 AAACC
11111 ABBBC
11111 CAAAB

The Serial num is a unique number and a primary key in the table and its tied to a specific order num .

What i have done so far :

WITH RECURSIVE New_Table (ord_nbr, Serial_Num,LVL) AS
SELECT ord_nbr, min (Serial_Num(VARCHAR(8000))) as Serial_Num,1
FROM Service_table
WHERE ord_nbr = '1111'
group by 1


SELECT C2.ord_nbr, trim(C1.Serial_Num) || ',' || trim(C2.Serial_Num), lvl+1
FROM Service_table as C1
inner join New_table as C2 on C1.ord_nbr = C2.ord_nbr and C1.Serial_Num>C2.Serial_Num
WHERE C1.ord_nbr = '1111'

SELECT ord_nbr, Serial_Num,LVL FROM New_Table
QUALIFY RANK() OVER(PARTITION BY ord_nbr order by Serial_Num desc ) = 1

This does the job but ... it pairs one serial number to another and runs all possible combinations till it reaches the last . so for orders that could have 100 or 200 serial numbers you quickly run out of spool space . I tried a lot of combinations without any success .. i would be grateful for ideas .

With Regards Enduro3333

Tags (1)