hadoop - ParseException in Hive query -

i running below hive query (mapr version 0.12):

select a.id, a.amt1, a.amt2 (   select id id, net_amount amt1   test_table   date_by >='2012-10-01' , date_by <='2012-10-31') q   join (     select id id, net_amount amt2     test_table     date_by >='2013-10-01' , date_by <='2013-10-31') r     on q.id=r.id ) 

but getting error:

error : failed: parseexception line 2:2 cannot recognize input near '(' 'select' 'id' in subquery source

there seems issue query, think should this:

select q.id,q.amt1,q.amt2   (select id id, net_amount amt1 test_table date_by >='2012-10-01' , date_by <='2012-10-31')  q  join (select id id ,net_amount amt2 test_table date_by >='2013-10-01' , date_by <='2013-10-31')  r on (q.id=r.id ) 

but comparing dates, , @antariksha ponited out need cast it. prefer better option, compare date in timestamp format. in case query :

select q.id,q.amt1,q.amt2   (select id id, net_amount amt1 test_table unix_timestamp(date_by,"<your date patern>") >=unix_timestamp('2012-10-01',"yyyy-mm-dd") , unix_timestamp(date_by,"<your date patern>") <=unix_timestamp('2012-10-31',"yyyy-mm-dd"))  q  join (select id id ,net_amount amt2 test_table unix_timestamp(date_by,"<your date patern>") >=unix_timestamp('2013-10-01',"yyyy-mm-dd") , unix_timestamp(date_by,"<your date patern>") <=unix_timestamp('2013-10-31',"yyyy-mm-dd"))  r on (q.id=r.id ) 

hope helps...!!!