CODEWITHSUNDEEP
sqlsubqueryimpala
Shriya Gupta
Shriya Gupta

Reputation: 43

Restructure a query in Impala/Hive that is using subquery to create new column in table

I am converting SQL query to Impala. The SQL query is using a subquery in select to create a new column and is as follows- 

select *, (select min(day)
           from date_series 
           where day > t.work_day) as next_work_day
from table1 t

However, Impala does not support subquery in select for creating new column and this query fails. Can I please get help to rewrite this query in a way Impala can execute.

Purpose of Query: Find the next working day for the work_day column.

Table1 is the outer table and contains

table1 contains 4 columns including the work day column

date_series contains all working dates stating from 2019-06-18 to current_day + 5 like

   2019-06-20
   2019-06-21
   2019-06-24
   .
   .

Upvotes: 1

Views: 791

Answers (2)

zealous
zealous

Reputation: 7503

You can re-write your query as following

select 
    t.*,
    work_day 
from table1 t 
join (
    select 
        min(day) as work_day 
    from date_series
) ds
on t.current_work_day = ds.work_day

where ds.work_day > t.current_work_day

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269573

I think you can do this:

select t.*, ds.next_day
from table1 t left join
     (select ds.*, lead(day) over (order by day) as next_day
      from date_series ds
     ) ds
     on t.current_work_day >= ds.day and
        (t.current_work_day < ds.next_day or ds.next_day is null);

Upvotes: 1

Related Questions