`Function 'stdev' with arity 1 not found` in mysql (denodo)

Question

I have a problem with calculating STDEV in mysql (denodo)

Here's the code:

select STDEV(amount) OVER (PARTITION BY customer_id ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
from my_table where

And this is the error I get.

[Code: 30100, SQL State: HY000] Function 'stdev' with arity 1 not found```

I've also tried to use SQRT(VAR(amount)) instead of stdev, but I've got

[Code: 30100, SQL State: HY000] Error computing capabilities of GROUP BY view` error.

How can I fix this?

Answer 1

If you're using PostgreSql driver this post might help you: https://community.denodo.com/answers/question/details?questionId=90670000000XcrdAAC&title=Function+%27current_setting%27+with+arity+1+not+found...

That error seems related to the PostgreSQL driver issuing a function not supported by VDP. I would use the JDBC driver to connect to VDP as this is the recommend way to connect as it is faster than the ODBC. The following KB article explains how to configure a JDBC connection from BO to VDP: https://community.denodo.com/kb/view/document/How%20to%20access%20VDP%20from%20SAP%20BusinessObjects?category=Northbound+Connections

In case you still want to connect using the ODBC connection, I think you should use the Denodo ODBC driver that is located in the installation path DENODO-HOME/tools/client-drivers/odbc.

Answer 2

It's my understanding you're encountering issues with the STDEV function and SQRT(VAR(amount)) in the version of Denodo you are using. The error messages suggest that the STDEV function with the given syntax is not available in your Denodo version. Additionally, the usage of SQRT(VAR(amount)) is not functioning due to a limitation with the GROUP BY view in Denodo.

For further assistance and possible solutions related to your specific Denodo version, you can refer to this link: Function 'current_setting' with arity 1 not found.

Hope this helps.