Hi @viswanath kammula. The Hive With clause/CTE (Common Table Expression) does not allow that type of nesting in its' syntax. In the documentation: https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression It states "The WITH clause is not supported within SubQuery Blocks." What you are trying to do could be accomplished (with a little different syntax) if you use a permanent view, or a temporary table. Also note: that same link, look at the bottom section titled "CTE in Views, CTAS, and Insert Statements." It shows you how to use CTEs (with clause) along with Views. The "View example" is similar to what yo want to do.
Here is a code example of a simple CTE/With statement (for the benefit of other people who will read this post):
with date_table as (select distinct year, quarter, month, dayofmonth
from flight_performance_orc_part
where yearmonth = '2016-12' limit 4)
select * from date_table d ; -- Or any other complex query, joins to other tables, etc.
Results:
year, quarter, month, dayofmonth
2016 4 12 1
2016 4 12 2
2016 4 12 3
2016 4 12 4
I hope this helps.
... View more
