With clause CTE redshift issue

ChatterBox
ChatterBox Member
edited March 2023 in Datasets

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

  • AS
    AS Coach
    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"

Answers

  • AS
    AS Coach

    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"
  • ChatterBox
    ChatterBox Member

    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();

  • AS
    AS Coach
    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"
  • ChatterBox
    ChatterBox Member

    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.

Categories