Recursive CTE using custom SQL

Question & Answer
, ,
1

Hi all,
I’m currently trying to implement recursive CTE using custom SQL in order o generate a sets of month which is needed for further joins. When I run below code, I’m getting such an error within QS SQL console. I’ve been searching through QS documentation but haven’t found a solution for this yet. Would appreciate your inputs, thanks!

image

Query:

WITH Months AS
(
SELECT
CAST(‘01-01-2021’ AS DATE) AS “MyDate”

UNION ALL

SELECT
“MyDate” + interval ‘1 month’
FROM Months
WHERE “MyDate” <= CAST(NOW() AS DATE)
)

select “MyDate” from Months

2

Hi @Rad ,

Test the recursive example for PostgreSQL and adapt it for your requirements .

Kind regards,
Koushik

1 Like
3

The problem seems to be incorrect SQL syntax from the datasource (not an issue with QuickSight) - that same query would give the same error if executed on postgresql directly. Try the query below:

WITH RECURSIVE Months AS
(
SELECT
CAST('01-01-2021' AS timestamp) AS MyDate

UNION ALL

SELECT
MyDate + interval '1 month'
FROM Months
WHERE MyDate <= CAST(NOW() AS DATE)
)

select MyDate from Months
2 Likes
4

@darcoli @Koushik_Muthanna Thank you for your help! I wasn’t aware that “RECURSIVE” command is needed in Postgre. Really appreciate you inputs, now working as expected.

1 Like