Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
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?
>>
>>
>>
>>
>>
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB