CODEWITHSUNDEEP
hivehiveql
user2462699
user2462699

Reputation: 439

HiveQL : use SELECT in clause WHERE

Is there a way to do this in HiveQL :

SELECT ......    

from
        default.thm_renta_produits_jour rpj
      WHERE
          rpj.co_societe = '${hiveconf:in_co_societe}'
      AND rpj.dt_jour >= (SELECT MIN(dt_jour) FROM default.calendrier WHERE co_an_semaine = '${hiveconf:in_co_an_sem}')

Because when i do this, i get this error :

FAILED: ParseException line 51:26 cannot recognize input near 'SELECT' 'MIN' '(' in expression specification

Thanks,

Upvotes: 0

Views: 3995

Answers (3)

Kim Moritz
Kim Moritz

Reputation: 163

I know that this is an old post, but the previous answers are now outdated. Newer versions of Hive (0.13+) support subqueries of where clauses, so your query should run.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries#LanguageManualSubQueries-SubqueriesintheWHEREClause

Upvotes: 0

Lukas Vermeer
Lukas Vermeer

Reputation: 5940

Hive does not support sub queries in the WHERE clause. Perhaps you can work around this by moving your sub query to a JOIN clause like so:

SELECT 
    rpj.*
FROM
    default.thm_renta_produits_jour rpj
JOIN
   (   SELECT MIN(dt_jour) AS min_dt_jour 
       FROM default.calendrier 
       WHERE co_an_semaine = '${hiveconf:in_co_an_sem}'
   ) m
WHERE
    rpj.co_societe = '${hiveconf:in_co_societe}'
    AND rpj.dt_jour >= m.min_dt_jour;

Hope that helps.

Upvotes: 0

Balaswamy Vaddeman
Balaswamy Vaddeman

Reputation: 8530

Hive does not support sub queries in where clause it supports sub queries in from clause only.

Upvotes: 3

Related Questions