CODEWITHSUNDEEP
sqlamazon-redshiftcommon-table-expressionrecursive-queryamazon-quicksight
Ryan White
Ryan White

Reputation: 1

Amazon QuickSight | Recursive CTE in subquery are not supported

I would like to use a recursive CTE to generate a series of dates. I am using this CTE to cross join onto locations to generate a mapping table as a CTE where each location has a row for each date. This query works great in my SQL Client (DBeaver), but Amazon QuickSight throws me the following:

Invalid operation: Recursive CTE in subquery are not supported.

SQL Query:

with recursive date_range(planned_date) as (
     select date(dateadd(day, -49, date(date_trunc('week', dateadd(day, 1, current_date)) - 1))) as planned_date
     union all
     select date(dateadd(day, 1, planned_date))
     from date_range
     where planned_date < date(dateadd(day, 1, current_date))
)
select * from date_range

I have seen others with similar issue on Tableau even, but no shared solution in forum yet. Are there any workarounds or is this case simply not available to perform on Amazon QuickSight without having to create a calendar table?

Upvotes: 0

Views: 72

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

The following "trick" leverages multiple cross joins with each join multiplying the number of rows (i.e. 10 X 10 X 10 X 10 rows) and the arithmetic used produces an integer from 0 that increments by 1 for each row. Sometimes known as a "tally table". This does not need to involve CTEs (although CTEs can be used when allowed, but this is not true here), If more rows are needed just add more cross joins and adjust the arithmetic accordingly.

Once the rows exist they can be used as needed, here they can produce a contiguous date series by using the generated integer within the dateadd function:

SELECT 
    num,
    '2025-01-01'::date + num AS new_date
FROM (
    SELECT 
        nk.digit * 1000 + nh.digit * 100 + nt.digit * 10 + n.digit AS num
    FROM (SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
          UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
          UNION ALL SELECT 8 UNION ALL SELECT 9) AS n -- 0 thru 9
    CROSS JOIN (SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
                UNION ALL SELECT 8 UNION ALL SELECT 9) AS nt -- thru 99
    CROSS JOIN (SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
                UNION ALL SELECT 8 UNION ALL SELECT 9) AS nh -- thru 999
    CROSS JOIN (SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 
                UNION ALL SELECT 8 UNION ALL SELECT 9) AS nk -- thru 9999
) AS tally
WHERE '2025-01-01'::date + num < '2025-02-01'
ORDER BY num;

See this db-fiddle

Upvotes: 0

Related Questions