Hive Query doesnt allow >= in subquery in the where clause

user_1197295 0
Viewed 762

I have a table (umt_date) which generates dates and I need to use this date in the second query to get all data greater than equal to the date in umt_date table

umt_date
----------------------------
|processdate||processname|
----------------------------
| 2020-06-01 | A         |
----------------------------
| 2020-06-01 | B         |

when I perform the sql

select * from main_table
where processdate >= (select processdate from umt_date where processname='A')

I get the following error

SQL Error [40000] [42000]: Error while compiling statement: FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression 'processdate': Only SubQuery expressions that are top level conjuncts are allowed

Since we do not have any common columns we cannot perform a join as well. Whats the alternative for this ?

hiveql subquery hive sql
2 Answers

If subquery returns single row, use cross join:

select m.* 
     from main_table m
          cross join (select processdate from umt_date where processname='A') t
where m.processdate >= t.processdate

And if the subquery returns more than one row, use join on some condition. cross join is not good in this case because it will duplicate data, though WHERE may filter duplicated rows

user_27003440

You could try using WITH clause like below

 WITH PROCESSA_SUBQ AS(
   select processdate from umt_date where processname='A'
 )
 SELECT a.* FROM main_table as a
 INNER JOIN
 PROCESSA_SUBQ as b
 ON 1=1    -- always true join condition, can be ignored.
 where a.processdate >= b.processdate

or you could get a boolean condition check too:

WITH PROCESSA_SUBQ AS(
   select processdate from umt_date where processname='A'
 )
 SELECT X.* FROM (
 SELECT a.*, (a.processdate>=b.processdate) as check FROM main_table as a
 INNER JOIN
 PROCESSA_SUBQ as b
 ON 1=1  -- always true join condition, can be ignored.
 ) as X where X.check = true
user_11317890
Related