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? > > > > >
