E. Pasma, Thank you, that gives me the results I was looking for, though I am still working on fully understanding why, I have to totally refresh my memory on the details of how the how nested set tree works, once I do, I am sure it will be clear.
----------------- OT: about Pax vobiscum I hope you don't mind, but since both you and Luuk mentioned my salutation, Pax vobiscum, I thought I would expand on it a bit... Luuk made the statement he had to look it up to know what type of peace I meant. It is my view that peace is not something that can be defined with some words, rather it is a universal experience. With the change in culture and world view the exact words used with regards to peace often change, the experience of true peace is always the same: I like to think of it as that experience of a deep breath and the desire to stay in that moment just a bit longer :) It is I hope the research Luuk did to learn more about my salutations brought him peace. If it did the opposite, I am very sorry, that was never my intent. My current motto in life is this statement: "Ultimately, we have just one moral duty: to reclaim large areas of peace in ourselves, more and more peace, and to reflect it toward others." -- Etty Hillesum (1914-1943) I totally understand 99.9999% of the folks that read my emails don't know what my salutation means. I actually do it more for me than for them. With me knowing that each email I send will end with Pax vobiscum it helps me keep myself in check to make sure the context I put in that email is one of peace. Let me tell ya, more than once I have written an email, looked at my salutation and gone back to seek a more peaceful tone. Pax vobiscum, Sam Carleton On Sun, Jun 16, 2019 at 1:33 PM E.Pasma <pasm...@concepts.nl> wrote: > > > Op 15 jun. 2019, om 19:20 heeft Sam Carleton <scarle...@gmail.com> het > volgende geschreven: > > > > I have kept reading and the next section of the book does away with the > > update and simply creates a recursive function. The goal of the function > > is to determine the 'weight' of a part by adding up all the > subassemblies * > > qty. At first I thought this might be easier to convert into SQLite, but > > upon deeper reading of the SQLite doc's, I am getting the impression this > > is NOT the type of thing the WITH statement can do. Below is the stored > > proc from the book, followed by the SQL to create the table and populate > > it. > > > > Can this be converted, if so, how? > > > > CREATE FUNCTION WgtCalc(IN MY_PART CHAR(2)) > > RETURNS INTEGER > > LANGUAGE SQL > > DETERMINISTIC > > -- RECURSIVE FUNCTION > > RETURN > > (select coalesce(sum(Subassemblies.qty * > > case when Subassemblies.lft + 1 = Subassemblies.rgt > > then subassemblies.wgt > > else WgtCalc(Subassemblies.part) > > end), max(Assemblies.wgt)) > > from Frammis as Assemblies > > left join Frammis as Subassemblies on > > Assemblies.lft < Subassemblies.lft and > > Assemblies.rgt > Subassemblies.rgt and > > not exists ( > > select * > > from frammis > > where lft < Subassemblies.lft and > > lft > Assemblies.lft and > > rgt > Subassemblies.rgt and > > rgt < Assemblies.rgt) > > where Assemblies.part = MY_PART); > > > > -- -------------------------------------- > > > > create table frammis > > ( > > part char, > > qty int, > > wgt int, > > lft int, > > rgt int > > ); > > > > create unique index frammis_part_uindex > > on frammis (part); > > > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('a', 1, 0, 1, > 28); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('b', 1, 0, 2, > 5); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('c', 2, 0, 6, > 19); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('d', 2, 0, 20, > 27); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('e', 2, 12, 3, > 4); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('f', 5, 0, 7, > 16); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('g', 2, 6, 17, > 18); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('h', 3, 0, 21, > 26); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('I', 4, 8, 8, > 9); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('j', 1, 0, 10, > 15); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('k', 5, 3, 22, > 23); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('l', 1, 4, 24, > 25); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('m', 2, 7, 11, > 12); > > INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('n', 3, 2, 13, > 14); > > > > Pax vobiscum, > > Sam Carleton > Hello, > I think it is just a regular tree walk. Below is a recursive CTE for that, > keeping track of the quantity and counting the weight of the leaves. > Message to Luuk: thanks for explaining Pax vobiscum. > E. Pasma > > > select part, > (-- correlated subquery to calculate the composed weight > with r as ( > select a.part, 1 as qty, a.wgt, a.lft, a.rgt > union all > select b.part, r.qty*b.qty, b.wgt, b.lft, b.rgt > from r > join Frammis as b > on b.lft > r.lft and > b.rgt < r.rgt and > not exists (-- condition to descend just one level at a > time > select * > from frammis as c > where > c.lft < b.lft and > c.lft > r.lft and > c.rgt > b.rgt and > c.rgt < r.rgt) > ) > select sum (r.qty*r.wgt) > from r > where not exists (-- condition to count only elementary parts > select * > from frammis as d > where > d.lft > r.lft and > d.rgt < r.rgt) > ) as sumwgt > from Frammis as a > ; > > a|682 > b|24 > c|272 > d|57 > e|12 > f|52 > g|6 > h|19 > I|8 > j|20 > k|3 > l|4 > m|7 > n|2 > > > > > > > > > > > > > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users