With clause CTE redshift issue
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.
Best Answer
-
Some thoughts here. You can definitely create procedures in MySQL in Domo. They've done some amazing things with procs.
I think maybe your code is inserting into an input table, which I'm not sure Domo will allow, but you vould try creating a temp table to do the same thing and then read from that completed temp table.
Regarding variables, in the past I have successfully assigned and read and incremented a variable within a single select statement (which could help you get around the need for a procedure for the loop), but this is not recommended according to the MySQL docs. I don't have access to that code anymore, though.
What about having a table of 12 rows, numbers 1 through 12, and adding it in your FROM clause but not joining on it to your revenue data, thereby iterating 12 times without creating a procedure to loop 12 times? Something to think about.
In any event, you can also contact Domo Support or your Domo consultant (if you have one) and they will get technical resources to help you out.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
With Redshift I don't think you can reference WITH clause views within themselves, like you might be able to with other databases. Amazon's WITH documentation and list of some unsupported features (where recursive CTEs are listed).
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Thank you Aaron. Is there any other way to achieve this objective apart from procedures in MySql. The objective is to populate the table with 12 rows (with split revenue) based on a revenue record which meets a certain business criteria. Following is the piece of Mysql procedure I've used: This doesn't give an error, but neither does it insert new rows:
create procedure test4_data()
BEGIN
Set @v_max = 13;
Set @v_counter = 1;
start transaction;
while @v_counter < @v_max do
insert into cb_sf_masterdata (FinalRevenue) select FinalRevenue from cb_sf_masterdata where adjustedrevenue >= 2500;
set @v_counter = @v_counter+1;
end while;
commit;
end;call test4_data();
0 -
Some thoughts here. You can definitely create procedures in MySQL in Domo. They've done some amazing things with procs.
I think maybe your code is inserting into an input table, which I'm not sure Domo will allow, but you vould try creating a temp table to do the same thing and then read from that completed temp table.
Regarding variables, in the past I have successfully assigned and read and incremented a variable within a single select statement (which could help you get around the need for a procedure for the loop), but this is not recommended according to the MySQL docs. I don't have access to that code anymore, though.
What about having a table of 12 rows, numbers 1 through 12, and adding it in your FROM clause but not joining on it to your revenue data, thereby iterating 12 times without creating a procedure to loop 12 times? Something to think about.
In any event, you can also contact Domo Support or your Domo consultant (if you have one) and they will get technical resources to help you out.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
Yes, it works now. I was trying to select from a table and insert into the same one. Creating a temporary table and selecting from it worked. Thank you.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive