Can you try it with, *UNIX_TIMESTAMP(table1.**created_time) = **prod.timestamps + (15 * **60* 1000)*
2012/7/21 Techy Teck <comptechge...@gmail.com> > My full query is like this- The thing that is in red color is giving me > exception. And I already have where clause after the JOIN. Not sure how to > make this thing work. > > *SELECT TT.BUYER_ID , COUNT(*) FROM* > *(SELECT table1.buyer_id, table1.item_id, table1.created_time from prod > RIGHT OUTER JOIN table1 ON (table1.item_id = prod.product_id AND > table1.BUYER_ID = prod.USER_ID AND ((UNIX_TIMESTAMP(table1.created_time) > - (prod.timestamps)) / 60* 1000 = 15)) where prod.product_id IS NULL > ORDER BY table1.buyer_id, table1.created_time desc) TT GROUP BY TT.BUYER_ID; > * > * > * > * > * > > > > > On Fri, Jul 20, 2012 at 3:49 PM, Bejoy KS <bejoy...@yahoo.com> wrote: > >> ** >> Hi >> >> AFAIK hive supports only equi joins. Can you check whether changing that >> <= to = , removes the semantic error? >> >> You can give conditions other than equality in a where clause post join. >> Regards >> Bejoy KS >> >> Sent from handheld, please excuse typos. >> ------------------------------ >> *From: * Techy Teck <comptechge...@gmail.com> >> *Date: *Fri, 20 Jul 2012 15:44:36 -0700 >> *To: *<user@hive.apache.org> >> *ReplyTo: * user@hive.apache.org >> *Subject: *Both Left and Right Aliases Encountered in Join >> >> I have a join query on which I am joining like this below. And it is >> working fine >> >> ON (table1.item = prod.product *AND* table1.buyer = prod.user *AND* >> UNIX_TIMESTAMP(table1.tt_time) >> = prod.timestamps) >> >> But when I tried to modify the above same thing. >> The thing that is in red color will work or not If I specify like this? >> As when I was running my query having joining like this below. >> >> ON (table1.item = prod.product *AND* table1.buyer = prod.user *AND* >> *((UNIX_TIMESTAMP(table1.tt_time) >> - (prod.timestamps)) / 60* 1000 <= 15)*) >> >> >> I am getting error like this- >> >> *FAILED: Error in semantic analysis: Both Left and Right Aliases >> Encountered in Join 15* >> * >> * >> * >> * >> So I guess there is some problem with number 15 at the end? Or we need to >> cast something? >> >> >> >> >> >
