Is 'Partition by' supported in DOMO SQL?

msharma

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

MarkSnodgrass

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)

jaeW_at_Onyx

@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