CODEWITHSUNDEEP
variableshivehiveql
Jim
Jim

Reputation: 35

How to use query results as variables in Hive SQL

I do not have access to shell wrapper script in hive sql, so I need help to revise below SQL only in Hive SQL scripts without shell.

Both of below SQL lines do not work, what I want to do is to retrieve data from mytable with dates between curr_date and Last_date, how to revise it?

set hivevar：curr_date = '2017-03-11';

set hivevar：Last_date = Select Max(dt) from tb_date;

select * from mytable where dt >= ${curr_date} and dt <= (${Last_date}); 

select * from mytable where dt between ${curr_date} and (${Last_date});

Upvotes: 1

Views: 210

Answers (1)

leftjoin
leftjoin

Reputation: 38290

No way to use variables for this. Use subquery:

set hivevar:curr_date='2017-03-11';

select t1.* 
  from 
      mytable t1 
      cross join (Select Max(dt) as max_dt from tb_date) dt
 where t1.dt >= ${curr_date} 
       and t1.dt <= dt.max_dt;

Upvotes: 1

Related Questions