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?

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.

-----Original Message-----
From: Techy Teck <[EMAIL PROTECTED]>
Date: Fri, 20 Jul 2012 15:44:36
To: <[EMAIL PROTECTED]>
Reply-To: [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?

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

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