Hi,
I'm trying to use a recursive CTE in Redshift to defer revenue over 13 periods ( iteration). The select statement after UNION ALL queries on the table deferredrevenue created within the scope of the "with" clause. The error here reads as relation "deferredrevenue" table name doesn't exist.
with deferredrevenue(opportunityid, finalrevenue, opportunityclosedate, currentmonthindex) AS
(
SELECT opportunityid, finalrevenue= adjustedrevenue/ 13, opportunityclosedate = opportunityclosedate, 1 AS currentmonthindex FROM cb_sf_masterdata where adjustedrevenue >=2500 and businessline IN('Profile New', 'Profile Renewal')
UNION ALL
SELECT opportunityid, finalrevenue, opportunityclosedate= DATEADD(month, 1, opportunityclosedate), currentmonthindex= currentmonthindex + 1 FROM "deferredrevenue" where currentmonthindex <13
)
SELECT * FROM deferredrevenue;
Does Domo allow recursions or am I missing something here? Any help is really appreciated. Thanks.