All Forums Database
Enduro3333 1 post Joined 03/14
27 Mar 2014
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

      UNION ALL

      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

You must sign in to leave a comment.