Is 'Partition by' supported in DOMO SQL?

I tried to create a new table in DOMO SQL using the following syntax in sql :

SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`,

sum(sum(case when `Transaction Type` = 1 then `Amount` else 0 end)) over (partition by `G_L Account No_`) as NewSum

 from g_l_entry

I get this error:


where as when I use Group BY I get no error.

SELECT `G_L Account No_`, sum(case when `Transaction Type` = 1 then `Amount` else 0 end) as NewSum

 from g_l_entry


Is Partition by not supported in DOMO SQL?


Thanks,

Monika

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Domo's MySQL is version 5.6, which doesn't support window functions. I would suggest using Magic ETL to do this with the rank and window tile. If you have Magic ETL 2.0, it will run incredibly fast.

    Rank and Window KB Article:

    Magic ETL 2.0 KB Article:

    https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/Creating_a_Magic_ETL_v2_DataFlow_(Beta)

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @msharma be specific about WHERE you are conducting your ETL.

    It's imprecise to call anything "Domo SQL" and no one will know exactly where you are in the product.

    @MarkSnodgrass is correct MySQL dataflows do not support Window functions. It's not because it's a 'Domo flavor of SQL'. MySQL dataflows are running in MySQL 5.6 databases. and 5.6 did not support Window functions.

    SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`,
    
    sum(sum(case when `Transaction Type` = 1 then `Amount` else 0 end)) over (partition by `G_L Account No_`) as NewSum
    
     from g_l_entry
    

    This is not appropriate SQL syntax. You wouldn't write a nested SUM(SUM())

    SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`,
    
    sum( case when `Transaction Type` = 1 then `Amount` else 0 end) over (partition by `G_L Account No_`) as NewSum
    
     from g_l_entry
    
    

    This is appropriate SQL Syntax. No, in MySQL / Redshift when writing SQL queries you are not required to include a GROUP BY clause.


    Given that you wrote Sum(Sum(Amount)) I assume you're trying to build a window function in Analyzer / Beast Modes. Again, while you might be tempted to call it Domo SQL, it is more precise to specify that you're writing queries that will hit Adrenaline (because that's the name of Domo's database layer).

    In that case, yes, Window functions ARE supported, but you must be applying a GROUP BY clause in the query that gets sent to Adrenaline via Analyzer. So you MUST have some sort of aggregation in place.

    Also, the interface between Analyzer and Adrenaline is a bit weird so yes, you MUST have the nested aggregate function SUM(SUM()) which is not standard SQL.


    https://www.youtube.com/watch?v=eifSYZIcPzg&t=523s

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!