CODEWITHSUNDEEP
hivesubqueryhiveql
Mark
Mark

Reputation: 347

Subqueries in HIVE

I have this nested subquery in HIVE. When I do 1 subquery my results return fine. However when I attempt to add the second subquery I receive the following error.

Prepare error: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:861 mismatched input '.' expecting ) near 'Subquery_1' in subquery source

Below is the nested subquery I'm attempting to implement. Mirroring this query in standard SQL works fine. But I am not very familiar with HQL and am unsure of where to start.

    LEFT OUTER JOIN
(SELECT  smz_au.mid,  
         smz_au.oid,  
         Subquery_1.oc
         Subquery_1.ri, 
         Subquery_1.riil,  
         Subquery_1.rrc
  FROM 
        smz_au
        LEFT OUTER JOIN
        (SELECT smz_au_1.oid
                smz_au_1.oc,
                smz_au_1.ri,
                smz_au_1.riil,
                smz_au_1.rrc
           FROM smz_au smz_au_1 
          WHERE (smz_au_1.initial_status = 'Allocated')
         ) Subquery_1 ON (smz_au.oid = Subquery_1.oid) 
  WHERE (smz_au.initial_status = 'Loan Start')
) Subquery ON (smz_ls.mid = Subquery.mid)

Upvotes: 1

Views: 790

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Missing comma after Subquery_1.oc before Subquery_1.ri, in the select.

     Subquery_1.oc --whithout comma here, Subquery_1.ri is an alias of Subquery_1.oc column
     Subquery_1.ri, --and alias should be without dot '.'
                    --this is why you got " mismatched input '.' "

Upvotes: 1

Related Questions