Hello,

I have the following situation:

There are some parts with parts inside

Part A has Part B inside

Part A has Part C inside

Part B has BA inside

Part B has BB inside

Part BA has BAA inside

Part BA has also BAB inside

Part BB has BBA inside

Part C has CA inside

A       ->       B        ->       BA     ->       BAA

A       ->       B        ->       BA     ->       BAB

A       ->       B        ->       BB     ->       BBA

A       ->       C        ->       CA

With a cte I can get every last parts, for example BA with BAA, BA with BAB, BB with BBA and C with CA. This is fine, but I would get the entire combination in a List

Instead of BA – BAA I would get A – B – BA – BAA.

Teilenummer is in this case the first left part, Matteilenr is the last part

Saved in the Table tmaterial

Teilenr (pteilenr)            Matteilenr            Anzahl (amount)

A                                   B                                    1

B                                    BA                                 10

BA                                 BAA                               10

BA                                 BAB                               5

B                                   BB                                  5

BB                                 BBA                               4

A                                   C                                    2

C                                    CA                                 10

The CTE: (tteile is just for the unit)

for with recursive ang as(

select a.matteilenr, a.teilenr as pteilenr, a.anzahl * :anzahlt as anzahl, b.einheitnr, a.kundennr from tmaterial a

left join tteile b on(a.matteilenr = b.teilenr)

where a.teilenr = :teilenr

union all

select aa.matteilenr, aa.teilenr as pteilenr, aa.anzahl * ang2.anzahl, ab.einheitnr, aa.kundennr from tmaterial aa

left join tteile ab on (aa.matteilenr = ab.teilenr)

inner join ang as ang2 on (aa.teilenr = ang2.matteilenr)

)

select a.matteilenr, a.pteilenr, sum(a.anzahl), a.einheitnr, a.kundennr from ang a

group by a.matteilenr, a.pteilenr, a.einheitnr, a.kundennr

into :materialnr, :pteilenr, :anzahlm, :einheit, :lieferant do suspend;

I give the cte the :teilenr (for Example A) and get every part itself and every block of two pairs. Now I would get the entire path, all layers.

Thank you.