1
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

Mohan
  • 867
  • 2
  • 7
  • 25
Rumana
  • 21
  • 1
  • 4

1 Answers1

1

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' )
Edward
  • 11
  • 2