Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Both Left and Right Aliases Encountered in Join


Copy link to this message
-
Re: Both Left and Right Aliases Encountered in Join
Navis류승우 2012-07-20, 23:26
Can you try it with,

*UNIX_TIMESTAMP(table1.**created_time) = **prod.timestamps + (15 * **60*
1000)*

2012/7/21 Techy Teck <[EMAIL PROTECTED]>

> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
>> *Date: *Fri, 20 Jul 2012 15:44:36 -0700
>> *To: *<[EMAIL PROTECTED]>
>> *ReplyTo: * [EMAIL PROTECTED]
>> *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?
>>
>>
>>
>>
>>
>