question multiple columns in where clause not working in HIVE in Archives of Support Questions (Read Only)

multiple columns in where clause not working in HIVE

Saransh — Fri, 16 Sep 2022 10:50:22 GMT

The errorThe query

EricL — Thu, 08 Dec 2016 10:45:05 GMT

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

Saransh — Thu, 08 Dec 2016 12:25:27 GMT

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

Saransh — Mon, 12 Dec 2016 07:10:26 GMT

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.

EricL — Wed, 14 Dec 2016 04:52:08 GMT

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:

It mentions the following:

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