topic Re: Recursive Table in MV in Data & Schema Discussions

Recursive Table in MV

nikhil_cr — Tue, 07 May 2024 17:46:01 GMT

Hi,

I have start date (File Date) and end date in the table and i want to repeat the rows for each day from start date to end date. Is there any way that I can achieve this in MV?

Please see screenshot -

Appreciate your help in Advance. Thanks.

Re: Recursive Table in MV

anurag — Tue, 07 May 2024 18:42:37 GMT

What is your use case? How will this be used?

what will your output look like? First column will be date, what about other columns?

Re: Recursive Table in MV

nikhil_cr — Tue, 07 May 2024 22:26:26 GMT

Hi @anurag,

I'm trying to pull the forecast details on a daily basis. I have this start date (File date) and end date, once I get the list of Rows between these two dates for each day - I can map them with my daily forecast table. I'm trying to achieve something similar to this - https://stackoverflow.com/questions/50380904/how-to-duplicate-records-according-to-start-and-end-dates

But it seems the recursive CTE logic is not working in MV. Can you please help me with some alternate method?

My output will look something like this - (Red being the start date and green being the end date)

Thanks,

Nikhil

Re: Recursive Table in MV

anurag — Wed, 08 May 2024 00:23:39 GMT

@nikhil_cr

Sample SQL to do this using MV:
SELECT explode(sequence(orderdate, shipdate, interval 1 day)) as date, Total_Sales FROM order_ship_date_query_result

Checkout this video as well:

https://youtu.be/u0GrrnK2ZHA

Re: Recursive Table in MV

nikhil_cr — Wed, 08 May 2024 11:26:13 GMT

@anurag Thank you so much. It is working as expected.