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

Switch to Threaded View
Hive, mail # user - Join not working in HIVE


Copy link to this message
-
Re: Join not working in HIVE
Nitin Pawar 2012-12-17, 13:03
are you trying to do a self join with less than and greater than without
having anything in where clause

I doubt that is going to work because less than and greater than will
always need a upper or lower limit to start the comparison (that includes
even in join statement)

so try something like

select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt) where
A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
On Mon, Dec 17, 2012 at 6:00 PM, Ramasubramanian Narayanan <
[EMAIL PROTECTED]> wrote:

> Hi,
>
> We are using Hive 0.7.0 and we are getting error while using "<=" along
> with "join"...
>
> Same query is working fine if we use "=".
>
> *Working Query :*
>
> hive> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
> A2.rgt from vprd A1 join vprd A2 on (A1.lft = A2.lft and A1.rgt = A2.rgt)
> group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
> Ended Job = job_201212162216_0019
> OK
> 1       1       4       Treasury Service        1       1000000
> 1       2       2       Root    2       1000
> 1       3       Z       CKC     2       1001
> 1       4       A       Treasury Service        2001    3000
> 1       5       OOAQ    CODE CASH MANAGEMENT    3       100
> 1       6       YP00    JPMC Treasury   101     200
> 1       7       432     Treasury Service        1002    1100
> 1       ID      CODE    SHORT_NAME      LFT     RGT
> Time taken: 22.234 seconds
>
> *Getting Error in the below Query :*
> *
> *
> hive> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
> A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >> A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;;
> *FAILED: Error in semantic analysis: Line 1:110 Both left and right
> aliases encountered in JOIN lft*
>
>
>
> Please let me know what is the reason for this error.. Is there any way to
> make it work?
>
> regards,
> Rams
>

--
Nitin Pawar