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