You can translate the question and the replies:

Microsoft Query (Excel) not Compatible to Denodo Query

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!
user
23-10-2019 05:44:22 -0400

3 Answers

Hi! I usually create reports in Microsoft Excel using the Denodo ODBC connection and I get the data without errors. You can check the query Excel is sending to VDP by enabling the When querying the VDP request log. I'd suggest to perform these steps: 1. Execute the below command in the VQL shell, ``` CALL LOGCONTROLLER(‘com.denodo.vdp.requests’, ‘INFO’) ``` 2. Execute the query in Excel 3. Check the vdp-requests.log file in the directory <Denodo-Home>\logs\vdp. With the requests log enabled you can get the query sent by Excel and execute it directly in the Denodo VQL Shell to ensure if it is valid so you will know if the issue is in the client side while generating the query. Hope this helps!
Denodo Team
24-10-2019 08:50:12 -0400
what I mean is not: I cannot create query in excel microsoft query. I mean it cannot create some advanced query, such as it contain some aggregation fucntion on some columns and group by other columns. Did you do that? Can you send me your microsoft query? no need very complicated, just contain one sum(amount_column) and group by other two attributes is enough. Can you send me your microsoft query??
user
 Edited on: 08-09-2020 01:28:06 -0400
Hello, I was able to execute my queries using Microft Query SQL Editor with GROUP BY, COUNT and some where conditions and they worked. This is my query: ``` SELECT bv_countries.region_id, Count(bv_countries.country_name) FROM admin.bv_countries bv_countries GROUP BY bv_countries.region_id ``` They are executed in Denodo and the results are returned and shown in Excel. If you enable the Requests log, as shown in the previous message, you can check if the query is being executed in the VDP server or not. If the query that fails is not shown in the logs you can review your client application configuration (MS Excel) to check if the error is in the client side. Hope this helps!
Denodo Team
05-11-2019 12:41:13 -0500
You must sign in to add an answer. If you do not have an account, you can register here