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