Support Questions
Find answers, ask questions, and share your expertise
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Impala Sql query to get date


Impala Sql query to get date

New Contributor

I have 2 Impala tables.

1st table T1 (additional columns are there but I am interested in only date and day type as weekday):

date       day_type
04/01/2020 Weekday
04/02/2020 Weekday
04/03/2020 Weekday
04/04/2020 Weekend
04/05/2020 Weekend
04/06/2020 Weekday

 2nd table T2:

process date       status
A       04/01/2020 finished
A       04/02/2020 finished
A       04/03/2020 run_again


Using impala queries i have to get the maximum date from second table T2 and get its status. According to the above table 04/03 is the maximum date.

If the status is finished on 04/03, then my query should return the next available weekday date from T1 which is 04/06/2020.

But if the status is run_again, then the query should return the same date. In the above table, 04/03 has run_again and when my query runs the output should be 04/03/2020 and not 04/06/2020.


What I tried so far: I ran a subquery from second table and got the maximum date and its status. i tried to run a case in my main query and gave t1 as subselect in Case statement but its not working. Looks like case statement does not allow a select statement within it. 

Is it possible to achieve this through Impala query?

Don't have an account?
Coming from Hortonworks? Activate your account here