Hi team,
I used excel to connect denodo for views data visualization, I follow this link: https://community.denodo.com/tutorials/browse/basics/4connect2odbcclient . And it works.
However, when I want to do some advanced query, not only get the detail data from view directly, I also want to implement aggregation on some columns. So I use the microsoft query function "Cycle Through Totals" to do it, and then the SQL generated is like below and it was not compatible with denodo so the error will prompt [Error: Syntax error: Exception parsing query near ''' java.sql.SQLException: Syntax error: Exception parsing query near '''; Error while executing the query] :
SELECT test_excel.column_a, test_excel.column_b, Sum(test_excel.amount1) AS 'Sum of amount1', Sum(test_excel.amount2) AS 'Sum of amount2'
FROM test.test_excel test_excel
WHERE (test_excel.filter1=2020092)
GROUP BY test_excel.column_a, test_excel.column_b
ORDER BY test_excel.column_a, test_excel.column_b
Above query also not work in denodo VQL Shell side. however If I double quote the database name "test", and double quoto the alias like "Sum of amount1" instead of single quote 'Sum of amount1' that microsoft query generate automatically, the query can run, and can also return data to excel from denodo. Query will be changed to below and it can return data to excel:
SELECT test_excel.column_a, test_excel.column_b, Sum(test_excel.amount1) AS "Sum of amount1", Sum(test_excel.amount2) AS "Sum of amount2"
FROM "test".test_excel test_excel
WHERE (test_excel.filter1=2020092)
GROUP BY test_excel.column_a, test_excel.column_b
ORDER BY test_excel.column_a, test_excel.column_b
My question is: how to prevent this kind of manual changes of microsoft query when excel query denodo views. Is there some setting in the ODBC driver side or the Excel Microsoft query side that we need to do, and then this kind of manual things can be omitted. Thanks!
