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 < ramasubramanian.naraya...@gmail.com> 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
