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 <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
