Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

multiple columns in where clause not working in HIVE

Solved Go to solution

multiple columns in where clause not working in HIVE

New Contributor

query_result_snip2.PNGThe errorquery_snip1.PNGThe query

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: multiple columns in where clause not working in HIVE

Guru

Hi,

 

I can see the images now, but not sure why they did not show up before. I can see that you are trying to run a query that contains:

 

(rel.i_prtitn,rel.i_mod_yr) in (select * from my_prtitn)

 

I would like you know that this is currently not supported, please refer to Hive's Apache documentation below:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries

 

It mentions the following:

 

  • IN/NOT IN subqueries may only select a single column

If you want to acheive the same goal, you will need to use JOIN with subQuery on those two columns.

 

Hope this helps.

 

Regards

Eric

 

 

4 REPLIES 4

Re: multiple columns in where clause not working in HIVE

Guru

Do I miss anything? I don't see what are in the two pictures..

Re: multiple columns in where clause not working in HIVE

New Contributor

Hi Eric,

 

The last line of the query 

 

where (rel.i_prtitn,rel.i_mod_yr) in (select * from my_prtitn) and rtrim(substr(rel.c_lu_1,3,2)) > ' ';

 

has the two columns "rel.i_prtitn" and "rel.i_mod_yr"  in the where clause for which the error is thrown.

 

Thanks,

Saransh

 

 

 

 

Re: multiple columns in where clause not working in HIVE

New Contributor

I guess this is a limitation of HIVE it can select only a single column for IN subqueries.

 

An Alternative to this can be:

 

where rel.i_prtitn in (select * from my_prtitn) and rel.i_mod_yr in (select * from my_prtitn) and rtrim(substr(rel.c_lu_1,3,2)) > ' ';

 

But it won't be an optimized solution. Please reply if you think otherwise.

 

Highlighted

Re: multiple columns in where clause not working in HIVE

Guru

Hi,

 

I can see the images now, but not sure why they did not show up before. I can see that you are trying to run a query that contains:

 

(rel.i_prtitn,rel.i_mod_yr) in (select * from my_prtitn)

 

I would like you know that this is currently not supported, please refer to Hive's Apache documentation below:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries

 

It mentions the following:

 

  • IN/NOT IN subqueries may only select a single column

If you want to acheive the same goal, you will need to use JOIN with subQuery on those two columns.

 

Hope this helps.

 

Regards

Eric