> Op 15 jun. 2019, om 19:20 heeft Sam Carleton <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
