Amazon QuickSight | Recursive CTE in subquery are not supported

Question

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?

not sure that generate_series is permitted in QuickSight but worth trying. If that fails have a look here: dbfiddle.uk/HcP-krhS this is a set of cross joins that can produce as many rows as you need (just add more cross joins) - once you have that you can set-up a series of dates. there are no CTEs involved — Paul Maxwell
– Paul Maxwell, Commented Dec 3, 2024 at 1:00
your question suggests sql-server syntax so for that syntax try this version: dbfiddle.uk/eE7GEtF6 — Paul Maxwell
– Paul Maxwell, Commented Dec 3, 2024 at 1:33
Apologies for not adding: this is Redshift SQL. Thank you Paul (@PaulMaxwell), your solution works for this use case. — Ryan White
– Ryan White, Commented Dec 3, 2024 at 14:28
Have added the approach as an answer so others with similar problem might benefit. If you are new here please read stackoverflow.com/help/someone-answers — Paul Maxwell
– Paul Maxwell, Commented Dec 5, 2024 at 4:17

Paul Maxwell · Accepted Answer · 2024-12-05 04:14:53Z

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

Collectives™ on Stack Overflow

Amazon QuickSight | Recursive CTE in subquery are not supported

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related