You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@asterixdb.apache.org by Steven Jacobs <sj...@ucr.edu> on 2017/05/15 21:09:47 UTC
CASE capabilities in SQL++
Hi all,
I'm trying to understand the capabilities of the "case" that we have in
SQL++. I tried the following queries and the two results don't seem to make
sense. Am I missing something?
SQL++:
use experiments
select count (country) from Tweets1;
select case when (select value count (country) from Tweets1) > 50 THEN
"true" ELSE "false" END;
RESULTS:
{ "$1": 36297735 }
{ "$1": "false" }
Thanks,
Steven
Re: CASE capabilities in SQL++
Posted by Steven Jacobs <sj...@ucr.edu>.
Got it, thanks.
Steven
On Mon, May 15, 2017 at 2:23 PM, Yingyi Bu <bu...@gmail.com> wrote:
> b is not a collection -- it binds to the values in side the collection by
> the subquery (select value
> count(DataverseName) from Metadata.`Dataset`), one at a time.
>
> On Mon, May 15, 2017 at 2:20 PM, Steven Jacobs <sj...@ucr.edu> wrote:
>
> > That recommendation worked. For informational purposes, the following
> also
> > worked:
> >
> > select case when b > 50 THEN "true" ELSE "false" END from(select value
> > count(DataverseName) from Metadata.`Dataset`) b ;
> >
> > It sounds like this shouldn't have worked though since b is a collection?
> >
> > Steven
> >
> > On Mon, May 15, 2017 at 2:16 PM, Yingyi Bu <bu...@gmail.com> wrote:
> >
> > > Any subquery returns a collection, so try the following:
> > >
> > > select case when (select value count (country) from Tweets1)[0] > 50
> > THEN
> > > "true" ELSE "false" END;
> > >
> > > On Mon, May 15, 2017 at 2:16 PM, Vignesh Raghunathan <
> > > vignesh.raghunathan90@gmail.com> wrote:
> > >
> > > > Hi Steven,
> > > >
> > > > Could you try indexing the result of the second 'select value
> count...'
> > > > statement?
> > > >
> > > > > use experiments
> > > > > select count (country) from Tweets1;
> > > > > select case when (select value count (country) from Tweets1)[0] >
> 50
> > > > THEN
> > > > > "true" ELSE "false" END;
> > > >
> > > >
> > > >
> > > > Thanks,
> > > > Vignesh
> > > >
> > > > > On May 15, 2017, at 5:09 PM, Steven Jacobs <sj...@ucr.edu>
> wrote:
> > > > >
> > > > > Hi all,
> > > > > I'm trying to understand the capabilities of the "case" that we
> have
> > in
> > > > > SQL++. I tried the following queries and the two results don't seem
> > to
> > > > make
> > > > > sense. Am I missing something?
> > > > >
> > > > > SQL++:
> > > > > use experiments
> > > > > select count (country) from Tweets1;
> > > > > select case when (select value count (country) from Tweets1) > 50
> > THEN
> > > > > "true" ELSE "false" END;
> > > > >
> > > > > RESULTS:
> > > > >
> > > > > { "$1": 36297735 }
> > > > >
> > > > > { "$1": "false" }
> > > > >
> > > > > Thanks,
> > > > > Steven
> > > >
> > >
> >
>
Re: CASE capabilities in SQL++
Posted by Yingyi Bu <bu...@gmail.com>.
b is not a collection -- it binds to the values in side the collection by
the subquery (select value
count(DataverseName) from Metadata.`Dataset`), one at a time.
On Mon, May 15, 2017 at 2:20 PM, Steven Jacobs <sj...@ucr.edu> wrote:
> That recommendation worked. For informational purposes, the following also
> worked:
>
> select case when b > 50 THEN "true" ELSE "false" END from(select value
> count(DataverseName) from Metadata.`Dataset`) b ;
>
> It sounds like this shouldn't have worked though since b is a collection?
>
> Steven
>
> On Mon, May 15, 2017 at 2:16 PM, Yingyi Bu <bu...@gmail.com> wrote:
>
> > Any subquery returns a collection, so try the following:
> >
> > select case when (select value count (country) from Tweets1)[0] > 50
> THEN
> > "true" ELSE "false" END;
> >
> > On Mon, May 15, 2017 at 2:16 PM, Vignesh Raghunathan <
> > vignesh.raghunathan90@gmail.com> wrote:
> >
> > > Hi Steven,
> > >
> > > Could you try indexing the result of the second 'select value count...'
> > > statement?
> > >
> > > > use experiments
> > > > select count (country) from Tweets1;
> > > > select case when (select value count (country) from Tweets1)[0] > 50
> > > THEN
> > > > "true" ELSE "false" END;
> > >
> > >
> > >
> > > Thanks,
> > > Vignesh
> > >
> > > > On May 15, 2017, at 5:09 PM, Steven Jacobs <sj...@ucr.edu> wrote:
> > > >
> > > > Hi all,
> > > > I'm trying to understand the capabilities of the "case" that we have
> in
> > > > SQL++. I tried the following queries and the two results don't seem
> to
> > > make
> > > > sense. Am I missing something?
> > > >
> > > > SQL++:
> > > > use experiments
> > > > select count (country) from Tweets1;
> > > > select case when (select value count (country) from Tweets1) > 50
> THEN
> > > > "true" ELSE "false" END;
> > > >
> > > > RESULTS:
> > > >
> > > > { "$1": 36297735 }
> > > >
> > > > { "$1": "false" }
> > > >
> > > > Thanks,
> > > > Steven
> > >
> >
>
Re: CASE capabilities in SQL++
Posted by Steven Jacobs <sj...@ucr.edu>.
That recommendation worked. For informational purposes, the following also
worked:
select case when b > 50 THEN "true" ELSE "false" END from(select value
count(DataverseName) from Metadata.`Dataset`) b ;
It sounds like this shouldn't have worked though since b is a collection?
Steven
On Mon, May 15, 2017 at 2:16 PM, Yingyi Bu <bu...@gmail.com> wrote:
> Any subquery returns a collection, so try the following:
>
> select case when (select value count (country) from Tweets1)[0] > 50 THEN
> "true" ELSE "false" END;
>
> On Mon, May 15, 2017 at 2:16 PM, Vignesh Raghunathan <
> vignesh.raghunathan90@gmail.com> wrote:
>
> > Hi Steven,
> >
> > Could you try indexing the result of the second 'select value count...'
> > statement?
> >
> > > use experiments
> > > select count (country) from Tweets1;
> > > select case when (select value count (country) from Tweets1)[0] > 50
> > THEN
> > > "true" ELSE "false" END;
> >
> >
> >
> > Thanks,
> > Vignesh
> >
> > > On May 15, 2017, at 5:09 PM, Steven Jacobs <sj...@ucr.edu> wrote:
> > >
> > > Hi all,
> > > I'm trying to understand the capabilities of the "case" that we have in
> > > SQL++. I tried the following queries and the two results don't seem to
> > make
> > > sense. Am I missing something?
> > >
> > > SQL++:
> > > use experiments
> > > select count (country) from Tweets1;
> > > select case when (select value count (country) from Tweets1) > 50 THEN
> > > "true" ELSE "false" END;
> > >
> > > RESULTS:
> > >
> > > { "$1": 36297735 }
> > >
> > > { "$1": "false" }
> > >
> > > Thanks,
> > > Steven
> >
>
Re: CASE capabilities in SQL++
Posted by Yingyi Bu <bu...@gmail.com>.
Any subquery returns a collection, so try the following:
select case when (select value count (country) from Tweets1)[0] > 50 THEN
"true" ELSE "false" END;
On Mon, May 15, 2017 at 2:16 PM, Vignesh Raghunathan <
vignesh.raghunathan90@gmail.com> wrote:
> Hi Steven,
>
> Could you try indexing the result of the second 'select value count...'
> statement?
>
> > use experiments
> > select count (country) from Tweets1;
> > select case when (select value count (country) from Tweets1)[0] > 50
> THEN
> > "true" ELSE "false" END;
>
>
>
> Thanks,
> Vignesh
>
> > On May 15, 2017, at 5:09 PM, Steven Jacobs <sj...@ucr.edu> wrote:
> >
> > Hi all,
> > I'm trying to understand the capabilities of the "case" that we have in
> > SQL++. I tried the following queries and the two results don't seem to
> make
> > sense. Am I missing something?
> >
> > SQL++:
> > use experiments
> > select count (country) from Tweets1;
> > select case when (select value count (country) from Tweets1) > 50 THEN
> > "true" ELSE "false" END;
> >
> > RESULTS:
> >
> > { "$1": 36297735 }
> >
> > { "$1": "false" }
> >
> > Thanks,
> > Steven
>
Re: CASE capabilities in SQL++
Posted by Vignesh Raghunathan <vi...@gmail.com>.
Hi Steven,
Could you try indexing the result of the second 'select value count...' statement?
> use experiments
> select count (country) from Tweets1;
> select case when (select value count (country) from Tweets1)[0] > 50 THEN
> "true" ELSE "false" END;
Thanks,
Vignesh
> On May 15, 2017, at 5:09 PM, Steven Jacobs <sj...@ucr.edu> wrote:
>
> Hi all,
> I'm trying to understand the capabilities of the "case" that we have in
> SQL++. I tried the following queries and the two results don't seem to make
> sense. Am I missing something?
>
> SQL++:
> use experiments
> select count (country) from Tweets1;
> select case when (select value count (country) from Tweets1) > 50 THEN
> "true" ELSE "false" END;
>
> RESULTS:
>
> { "$1": 36297735 }
>
> { "$1": "false" }
>
> Thanks,
> Steven