AW: AW: [firebird-support] CTE difficult question
Hello Karol,
thank you, that was the one piece I was looking for, the right position for the
listing.
Best thanks.
Regards.
Olaf
Von: firebird-support@yahoogroups.com
Gesendet: Mittwoch, 13. November 2019 09:51
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] CTE difficult question
Hi,
from your description i really do not know what is working for you and what is
not working.
And your expectation.
but to understand recursive CTE look at simple sample. Recursive CTE work
throught tree.
### metadata ###
CREATE TABLE TEST_TREE
(
ID INTEGER NOT NULL,
ID_HEADER INTEGER,
CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
### test data ###
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
### test query ###
WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL
FROM TEST_TREE TT
WHERE TT.ID_HEADER IS NULL
UNION ALL
SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1
FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
)
SELECT
*
FROM
R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
###
run it and then addapt to your needs, as your situation looks same to me
regards,
Karol Bieniaszewski
Re: AW: [firebird-support] CTE difficult question
Hi,
from your description i really do not know what is working for you and what is
not working.
And your expectation.
but to understand recursive CTE look at simple sample. Recursive CTE work
throught tree.
### metadata ###
CREATE TABLE TEST_TREE
(
ID INTEGER NOT NULL,
ID_HEADER INTEGER,
CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
### test data ###
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
### test query ###
WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL
FROM TEST_TREE TT
WHERE TT.ID_HEADER IS NULL
UNION ALL
SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1
FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
)
SELECT
*
FROM
R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
###
run it and then addapt to your needs, as your situation looks same to me
regards,
Karol Bieniaszewski
AW: [firebird-support] CTE difficult question
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 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)MatteilenrAnzahl (amount) A B1 BBA 10 BA BAA 10 BA BAB 5 B BB 5 BB BBA 4 A C2 CCA 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.
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)MatteilenrAnzahl (amount) A B1 BBA 10 BA BAA 10 BA BAB 5 B BB 5 BB BBA 4 A C2 CCA 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.
