Sorry, I don't want to annoy, but can I do this with a separate list-function or is it possible to realize it with new functions in firebird 4?
TA TB A B B is a part from A A C C is a part from A A D D is a part from A D X X is a part from D, X is material D Y Y is a part from D, Y is material C Z Z is a part from C, Z is material B Z Z is a part from B, Z is material Now I would get all from A with Material: A - B - Z1, material:Z1 A - C - Z, material: Z A - D - X, material: X A - D - Y, material: Y Later I can build a sum from all materials, for example Z Thank you. Von: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> Gesendet: Dienstag, 12. November 2019 14:23 An: firebird-support@yahoogroups.com Betreff: AW: [firebird-support] CTE difficult question Ist it possible somehow? I would get each Part and the complete Path for it. If the Part is a material, I would get it and the complete list with all parts from the first til the material itself. Thanks. ---- 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.
