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 On Sat, Jun 15, 2019 at 12:29 PM Sam Carleton <scarle...@gmail.com> wrote: > I am working with a nested set tree and reading through Joe Celko's trees > and hierarchies in sql source book to refresh the old brain. In section > 4.7.1 of the book he has an example that uses a do while statement. It > looks like this concept can be implemented in SQLite by using the WITH > clause, but I don't fully understand it. Might someone be able to help me > translate this into SQLite code? Here is the beginnings of it: > > while exists (select * from frammis where wgt = 0) > do update frammis > set wgt = ... > end while; > > The ... is the following case statement, unless I am mistaken this is > pretty straight forward to convert, my big question is the code above. > > case > when 0 < all ( select c.wgt > from frammis as c > left outer join frammis as b on b.lft = (select max(S.lft) > from frammis as s where c.lft > s.lft and c.lft < s.rgt) > where b.part = frammis.part ) > then ( select coalesce (sum(c.wgt*c.qty), b.wgt) > from frammis as c > left outer join frammis as b on b.lft = (select max(S.lft) from > frammis as s where c.lft > s.lft and c.lft < s.rgt) > where b.part = frammis.part ) > else frammis.wgt > end > > Pax vobiscum, > Sam Carleton > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users