Subqueries in HIVE

Question

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)

leftjoin · Accepted Answer · 2020-01-07T09:21:56.497

1

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 '.' "

edited Jan 07 '20 at 09:21

answered Jan 07 '20 at 09:06

leftjoin

36,950
8
57
116

Wow. I feel stupid. Thank you for noticing that. I completely missed that. Much appreciated! – Mark Jan 07 '20 at 13:59

Subqueries in HIVE

1 Answers1