CODEWITHSUNDEEP
joinhiveqlin-subquery
Rumana
Rumana

Reputation: 21

SemanticException IN encountered with 0 children error in HIVE

Select t.id, sa.APPLICATION_ID, c.CODE_SID  
from table1 t 
join table2 c 
on c.ID = t.COSTTYPE 
and  c.TYPE_LIST_ID 
IN (Select typel.TYPE_LIST_ID 
     from table3 typel
     where typel.TYPELIST_TABLE_NAME = 'a' )
join table4 tl 
    on t.id = tl.id 
join table5 sa 
where sa.sourcename='GW' and sa.SYSTEM_APPLICATION_SHORT_NAME = 'B'

I am trying to do this in Hive but I get and error saying SemanticException IN encountered with 0 children. So the problem is with the IN (Select.......). Is there any other way that I can write my query in without using the IN clause? Thanks for your help in advance

Upvotes: 1

Views: 8340

Answers (1)

Edward
Edward

Reputation: 11

I think the issue with your IN clause is that it's stated outside of your WHERE clause? Try moving that portion into your WHERE clause like so:

Select t.id, sa.APPLICATION_ID, c.CODE_SID  
from table1 t 
join table2 c 
on c.ID = t.COSTTYPE 
join table4 tl 
    on t.id = tl.id 
join table5 sa 
where sa.sourcename='GW' and sa.SYSTEM_APPLICATION_SHORT_NAME = 'B'
and  c.TYPE_LIST_ID 
IN (Select typel.TYPE_LIST_ID 
     from table3 typel
     where typel.TYPELIST_TABLE_NAME = 'a' )

Upvotes: 1

Related Questions