Understanding the WITH clause

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

Understanding the WITH clause

Sam Carleton
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

Sam Carleton
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 <[hidden email]> 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
[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

E.Pasma

> Op 15 jun. 2019, om 19:20 heeft Sam Carleton <[hidden email]> 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
[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 16-6-2019 19:32, E.Pasma wrote:
> Message to Luuk: thanks for explaining Pax vobiscum.
> E. Pasma

Thanks for the recursive CTE.

Luuk

_______________________________________________
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

Sam Carleton
In reply to this post by E.Pasma
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 <[hidden email]> wrote:

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

James K. Lowden
On Mon, 17 Jun 2019 20:46:41 -0400
Sam Carleton <[hidden email]> wrote:

> It is my view that peace is not something that can be defined with
> some words, rather it is a universal experience.  

        "But peace is not merely the absence of this tension, but the
presence of justice."
        -- MLK, Letter from the Birmingham Jail

--jkl
_______________________________________________
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: OT!!!!! Understanding the WITH clause

Sam Carleton
James,

That is a really, really interesting quote.  it really got me thinking
about peace and the definition of it.  It got me thinking so much I wanted
to know the context of the sentence, so I went looking for it in Letter
from the Birmingham Jail, this is my source link (
http://web.cn.edu/kwheeler/documents/letter_birmingham_jail.pdf).  I am not
able to find that sentence in the PDF.

Ah, hold the press, I searched a bit more and discovered that what you
quote is not a direct quote but a paraphrased version of this sentence:

I have almost reached the regrettable conclusion that the Negro's great
stumbling block in the stride toward freedom is not the White Citizens
Councillor or the Ku Klux Klanner but the white moderate who is more
devoted to order than to justice; who prefers a negative *peace* which is
the *absence of tension* to a positive peace which i*s the presence of
justice*; who constantly says, "I agree with you in the goal you seek, but
I can't agree with your methods of direct action"; who paternalistically
feels that he can set the timetable for another man's freedom; who lives by
the myth of time; and who constantly advises the Negro to wait until a
"more convenient season."

Here is what I learned from this.  I was WRONG!!!!!  I made a very arrogant
statement that everyone knows what peace is, as if there is only one
meaning of peace!

MLK is talking about a very different type of peace then what I am talking
about.  The way I read what MLK is talking about, I get the impression he
is talking about civil peace, aka everyone following the laws the civil
athorities have put in place.  As I think most folks get, today, many of
these laws were not so good and thus denied folks justice.

I was referring to a feeling of inner peace.  For example:  Today I learned
my company is terminating my employment as of Oct 1.  I'm at "inner" peace
with that.

James, thank you so much for pointing out that quote and modivating me to
dig into it, I learned a lot tonight, which helps me continue to maintain
that inner peace after such an "interesting" day.


Pax vobiscum,
Sam Carleton


On Tue, Jun 18, 2019 at 2:57 PM James K. Lowden <[hidden email]>
wrote:

> On Mon, 17 Jun 2019 20:46:41 -0400
> Sam Carleton <[hidden email]> wrote:
>
> > It is my view that peace is not something that can be defined with
> > some words, rather it is a universal experience.
>
>         "But peace is not merely the absence of this tension, but the
> presence of justice."
>         -- MLK, Letter from the Birmingham Jail
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: OT!!!!! Understanding the WITH clause

Michael Falconer
SELECT peace FROM disaster WHERE disaster.cause = 'Windows';

Returns: *nix


On Thu, 20 Jun 2019 at 14:26, Sam Carleton <[hidden email]> wrote:

> James,
>
> That is a really, really interesting quote.  it really got me thinking
> about peace and the definition of it.  It got me thinking so much I wanted
> to know the context of the sentence, so I went looking for it in Letter
> from the Birmingham Jail, this is my source link (
> http://web.cn.edu/kwheeler/documents/letter_birmingham_jail.pdf).  I am
> not
> able to find that sentence in the PDF.
>
> Ah, hold the press, I searched a bit more and discovered that what you
> quote is not a direct quote but a paraphrased version of this sentence:
>
> I have almost reached the regrettable conclusion that the Negro's great
> stumbling block in the stride toward freedom is not the White Citizens
> Councillor or the Ku Klux Klanner but the white moderate who is more
> devoted to order than to justice; who prefers a negative *peace* which is
> the *absence of tension* to a positive peace which i*s the presence of
> justice*; who constantly says, "I agree with you in the goal you seek, but
> I can't agree with your methods of direct action"; who paternalistically
> feels that he can set the timetable for another man's freedom; who lives by
> the myth of time; and who constantly advises the Negro to wait until a
> "more convenient season."
>
> Here is what I learned from this.  I was WRONG!!!!!  I made a very arrogant
> statement that everyone knows what peace is, as if there is only one
> meaning of peace!
>
> MLK is talking about a very different type of peace then what I am talking
> about.  The way I read what MLK is talking about, I get the impression he
> is talking about civil peace, aka everyone following the laws the civil
> athorities have put in place.  As I think most folks get, today, many of
> these laws were not so good and thus denied folks justice.
>
> I was referring to a feeling of inner peace.  For example:  Today I learned
> my company is terminating my employment as of Oct 1.  I'm at "inner" peace
> with that.
>
> James, thank you so much for pointing out that quote and modivating me to
> dig into it, I learned a lot tonight, which helps me continue to maintain
> that inner peace after such an "interesting" day.
>
>
> Pax vobiscum,
> Sam Carleton
>
>
> On Tue, Jun 18, 2019 at 2:57 PM James K. Lowden <[hidden email]>
> wrote:
>
> > On Mon, 17 Jun 2019 20:46:41 -0400
> > Sam Carleton <[hidden email]> wrote:
> >
> > > It is my view that peace is not something that can be defined with
> > > some words, rather it is a universal experience.
> >
> >         "But peace is not merely the absence of this tension, but the
> > presence of justice."
> >         -- MLK, Letter from the Birmingham Jail
> >
> > --jkl
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Regards,
     Michael.j.Falconer.
_______________________________________________
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: OT!!!!! Understanding the WITH clause

Luuk

On 20-6-2019 08:11, Michael Falconer wrote:
> SELECT peace FROM disaster WHERE disaster.cause = 'Windows';
>
> Returns: *nix
>
>
The query should be

SELECT peace
FROM disaster
WHERE (disaster.cause LIKE '%Windows%'
        OR disaster.cause LIKE '%nix%'
        OR disaster.cause LIKE '%incompetence%'
        );


Also then return value might be different, depending on the time of day
and possible other, at present not known, parameters.



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