KB485005: How to avoid SQL syntax errors in Exasol by enabling Unified Quoting in MicroStrategy

Background

In rare cases it can happen that you might receive SQL errors like the following examples when SQL´s (generated from reports) are pushed down from Strategy to Exasol:
"[EXASOL][EXASolution driver]syntax error, unexpected DATE_, expecting UNION_ or EXCEPT_ or MINUS_ or INTERSECT_ "
"[EXASOL][EXASolution driver]syntax error, unexpected TIMESTAMP_, expecting UNION_ or EXCEPT_ or MINUS_ or INTERSECT_ "
"[EXASOL][EXASolution driver]syntax error, unexpected UPDATE_, expecting UNION_ or EXCEPT_ or MINUS_ or INTERSECT_ "

Root Cause

The root cause for that problem can most likely be found in the structure of your data tables, views or queries.
You should generally avoid using so-called "identifiers" (SQL reserved words) as column names (e.g. DATE, TIMESTAMP, UPDATE, BEGIN, END, CHECK, TRUE, FALSE, etc.) when creating your tables/views or setting aliases in SQL queries.
But... as we all know, sometimes you cannot avoid this due to given data structures.
Example - The following query will fail with the error: 

error message:
[EXASOL][EXASolution driver]syntax error, unexpected DATE_, expecting UNION_ or EXCEPT_ or MINUS_ or INTERSECT_ 

query:
SELECT
a.CUSTOMER_ID   CUSTOMER_ID,
a.CUSTOMER_NAME CUSTOMER_NAME,
a.DATE          DATE
FROM
X.CUSTOMER a
WHERE
a.CUSTOMER_ID = 1234;

So the query needs to be modified to run without errors. In this case the column and alias, that causes the error (DATE) must be quoted with double quotes as shown in the following example:

SELECT
a.CUSTOMER_ID CUSTOMER_ID,
a.CUSTOMER_NAME CUSTOMER_NAME,
a."DATE" "DATE"
FROM
X.CUSTOMER a
WHERE
a.CUSTOMER_ID = 1234;

To solve this issue when pushing down SQL from Strategy to Exasol you must enable "Unified Quoting" in your Strategy environment.
 

Prerequisites

Unifying the quoting behaviour is available since the release of Strategy 2020 and described in the following Strategy Knowledge Base article: KB483540
To implement the behaviour also for database connections to Exasol, the following steps need to be done with the release of Strategy 2020. Strategy 2021 supports the unified quoting for Exasol out of the box and no further configurations are needed.
 

Solution

How to enable Unified Quoting in Strategy 2020 for Exasol

Step 1
Ensure that all of your projects are migrated to Strategy 2020 when upgrading from a previous Strategy version, see Strategy Knowledge Base article KB483540

• Upgrade the Data Engine Version
• Upgrade the Metadata

Step 2
Install the new database object "new_database_m2021.pds" as described in chapter 3 of the Strategy Knowledge Base article Exasol 6.x
Be sure to change all of your existing Exasol Database Connections in Strategy to the newly installed database object (Database Connection Type) "EXAsolution 6.x" and check all connection parameters accordingly as described in the Exasol Documentation
All relevant steps are described in Strategy Knowledge Base article KB43537
 
The following steps 3 and 4 are only needed if you set up the connection to Exasol for the first time or your Exasol Database Version has changed.
 
Step 3
Download the latest Exasol JDBC driver (or ODBC driver) from the Exasol Download section V7.0 or V6.2 (according to your database version)
 
Step 4
Install the latest Exasol JDBC driver (or ODBC driver) on each Strategy Intelligence Server in the cluster as described in chapter 4 of the Strategy Knowledge Base article Exasol 6.x. Do not forget to restart the Intelligence Server after installing the driver. You might also follow chapter 5 and 6 in the KB article.