Is 'Partition by' supported in DOMO SQL?

Options
msharma
msharma Member
edited March 2023 in SQL DataFlows

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:

image.png


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 Coach
    Options

    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:

    https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

    Magic ETL 2.0 KB Article:

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

    **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.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Options

    @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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories