You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Karthik <ka...@yahoo.com> on 2010/10/12 02:42:18 UTC
IN clause usage in "where"
Is there a way I can use the "in" list of values in a query condition like
"select col1 from table1 where col1 in ('abc','xyz')"?
I use Hive 0.6.
Regards,
Karthik.
Re: IN clause usage in "where"
Posted by John Sichi <js...@facebook.com>.
find_in_set is documented on this page:
http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF
But Hive 0.6 also supports the IN syntax (HIVE-801).
JVS
On Oct 12, 2010, at 10:23 AM, Karthik wrote:
Neil,
Thanks a lot, that works like a charm. Perhaps, that should go into the Hive Wiki page too sometime.
Regards,
Karthik.
________________________________
From: Neil Xu <ne...@gmail.com>>
To: user@hive.apache.org<ma...@hive.apache.org>
Sent: Mon, October 11, 2010 6:42:10 PM
Subject: Re: IN clause usage in "where"
you can try find_in_set(string str, string strList) , ex.
select col1 from table1 where find_in_set(col1, 'abc,xyz')<>0;
if col1 has NULL values, it can be modified as
select col1 from table1 where find_in_set( coalesce(col1,'') , 'abc,xyz')<>0;
the details about find_in_set are at https://issues.apache.org/jira/browse/HIVE-688
Neil
2010/10/12 Karthik <ka...@yahoo.com>>
Is there a way I can use the "in" list of values in a query condition like
"select col1 from table1 where col1 in ('abc','xyz')"?
I use Hive 0.6.
Regards,
Karthik.
Re: IN clause usage in "where"
Posted by Karthik <ka...@yahoo.com>.
Neil,
Thanks a lot, that works like a charm. Perhaps, that should go into the Hive
Wiki page too sometime.
Regards,
Karthik.
________________________________
From: Neil Xu <ne...@gmail.com>
To: user@hive.apache.org
Sent: Mon, October 11, 2010 6:42:10 PM
Subject: Re: IN clause usage in "where"
you can try find_in_set(string str, string strList) , ex.
select col1 from table1 where find_in_set(col1, 'abc,xyz')<>0;
if col1 has NULL values, it can be modified as
select col1 from table1 where find_in_set( coalesce(col1,'') , 'abc,xyz')<>0;
the details about find_in_set are at
https://issues.apache.org/jira/browse/HIVE-688
Neil
2010/10/12 Karthik <ka...@yahoo.com>
Is there a way I can use the "in" list of values in a query condition like
>"select col1 from table1 where col1 in ('abc','xyz')"?
>
>I use Hive 0.6.
>
>Regards,
>Karthik.
>
>
Re: IN clause usage in "where"
Posted by Neil Xu <ne...@gmail.com>.
you can try find_in_set(string str, string strList) , ex.
*select col1 from table1 where find_in_set(col1, 'abc,xyz')<>0;*
if col1 has NULL values, it can be modified as
*select col1 from table1 where find_in_set( *coalesce(*col1,'') ,
'abc,xyz')<>0;*
the details about find_in_set are at
https://issues.apache.org/jira/browse/HIVE-688
Neil
2010/10/12 Karthik <ka...@yahoo.com>
> Is there a way I can use the "in" list of values in a query condition like
> "select col1 from table1 where col1 in ('abc','xyz')"?
>
> I use Hive 0.6.
>
> Regards,
> Karthik.
>
>