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 >> Join not working in HIVE


Copy link to this message
-
Re: Join not working in HIVE
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
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