Subject AW: [firebird-support] CTE difficult question
Author Check_Mail
Post date 2019-11-13T07:24:56Z

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.