Understanding the WITH clause

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Understanding the WITH clause

Sam Carleton-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Understanding the WITH clause

Luuk

On 15-6-2019 18:24, Sam Carleton wrote:
> Pax vobiscum,
> Sam Carleton

I had to pull up a dictionary to know what Pax (=Peace) you are talking
about..

(https://en.wikipedia.org/wiki/Pax_(liturgy))

"In Christian <https://en.wikipedia.org/wiki/Christianity>liturgy
<https://en.wikipedia.org/wiki/Liturgy>, "the /*Pax*/" is an
abbreviation of the Latin
<https://en.wikipedia.org/wiki/Ecclesiastical_Latin>salutations "/pax
vobis/" ("peace to you") or "/pax vobiscum/" ("peace with you"), which
are used in the Catholic
<https://en.wikipedia.org/wiki/Catholic_Church>Mass
<https://en.wikipedia.org/wiki/Mass_(liturgy)>and Lutheran
<https://en.wikipedia.org/wiki/Lutheranism>Divine Service
<https://en.wikipedia.org/wiki/Divine_Service_(Lutheran)>."

I do think (without any expressed opinion here) that there's is olny a
small percentage of people who did know withoutout searching the
internet (of the dictionary at home).

Greetings from Holland,
"*Holland*is a region and former province on the western coast of the
Netherlands <https://en.wikipedia.org/wiki/Netherlands>. The name
/Holland/is also frequently used informally to refer to the whole of the
country of the Netherlands. This usage is commonly accepted in other
countries,^[2] <https://en.wikipedia.org/wiki/Holland#cite_note-MoFA-2>
and sometimes employed by the Dutch themselves.^[2]
<https://en.wikipedia.org/wiki/Holland#cite_note-MoFA-2> However, some
in the Netherlands, particularly those from regions outside Holland, may
find it undesirable^[2]
<https://en.wikipedia.org/wiki/Holland#cite_note-MoFA-2> or
misrepresentative to use the term for the whole country."
(https://en.wikipedia.org/wiki/Holland)

Yes, i'm one of the people who do find Holland misrepresentative for
'the Netherlands' (https://en.wikipedia.org/wiki/Netherlands)

But, since drifting away from topic,

Houdoe (https://en.wikipedia.org/wiki/Houdoe)





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users