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 Plain View
Hive >> mail # user >> Join not working in HIVE


Copy link to this message
-
Join not working in HIVE
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 2012-12-17, 13:03
+
Ramasubramanian Narayanan... 2012-12-17, 13:20
+
Nitin Pawar 2012-12-17, 13:23
+
Ramasubramanian Narayanan... 2012-12-17, 13:47
+
Nitin Pawar 2012-12-17, 13:55
+
Philip Tromans 2012-12-17, 14:18
+
Alexander Pivovarov 2012-12-17, 20:45
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