How to do generate_series in a SQL dataflow

datadiva
datadiva Member
in Magic ETL

Hi,

 

I'm trying to elimate the need for a database between our production system and Domo by doing all ETL work within Domo (I know this may take longer to process).  The database is PostgreSQL and some of the queries that I need to do in Domo use a function called generate_series, but it appears there is not a simple MySQL equivalent to this.  I'm trying to do a SQL dataflow for the ETL, but need help figuring out how to code that function to work in Domo.  Here's one of the simpler examples where this function is being used:

 

SELECT id, owner, date_trunc('MONTH',day)::DATE AS date, SUM(amount/days)AS splitamount
FROM(SELECT*,end_date-start_date+1 AS days,generate_series(start_date, end_date, INTERVAL '1 day')::DATE AS DAY
FROM opportunity) k
WHERE NOT EXISTS(SELECT oppty_id
FROM orders t
WHERE t.oppty_id=k.id)
GROUP BY 1,2,3;

 

Can anyone help advise how to make the same thing happen in MySQL?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Domo has a connector called Domo Dimensions that is a data table of all calendar dates that is useful for this type of work. If you search for calendar in the connectors section of the App Store, it should show up. If it doesn't ask your CSM to enable it. This can save you some work when needing to build a table full of dates. 

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • RobertoBis
    RobertoBis Member

    Have you tried this?

     

    select date from (
        select
            date_format(
            adddate(start_date, @num:=@num+1),
            '%Y-%m-%d'
        ) date
        from
            orders,
        (select @num:=-1) num
    ) as dt

     

    Hope this can help

     

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Domo has a connector called Domo Dimensions that is a data table of all calendar dates that is useful for this type of work. If you search for calendar in the connectors section of the App Store, it should show up. If it doesn't ask your CSM to enable it. This can save you some work when needing to build a table full of dates. 

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • datadiva
    datadiva Member

    The trouble with the first possibility is that I need to have a specific end date as well as a start date.  This serires is used towards calculating when and how long a campaign ran to understand the revenue generated from it. 

     

    I'll look into the Domo Dimensions as that sounds like it might be a good option and help with the other SQL statements I need to work out for this too. 

Categories