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
