multiple columns in where clause not working in HIVE

[#1]

The errorThe query

[#2]

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

[#3]

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

[#4]

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.

[#5 · Accepted solution]

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