KB483318: SQL error due to unified quoting pattern not being applied to Freeform SQL subquery

Symptoms

• The project-level VLDB setting "Quoting Behavior" is enabled (set to "1") in order to apply the Unified Quoting pattern.
• Data Engine Version is set to 12
• Report is using Freeform SQL, for example, a logical table defined in Freeform SQL.
• ODBC Error is encountered when running a report

Glossary

For more information on Unified Quoting Behavior and Freeform SQL, please refer the Strategy knowledge base articles on "Unified Quoting Behavior for Warehouse Identifiers" and "Overview of Freeform SQL reports"

• Unified Quoting Pattern
• Freeform SQL

Example

A report using a table that is created with FreeForm SQL, before Unified Quoting feature is introduced, its query looks like (the sub-query in red is table defined with FreeForm SQL):

select    sum(pa0.TOT_DOLLAR_SALES) Column1,
    pa2.REGION_ID REGION_ID,
    pa2.REGION_NAME REGION_NAME,
    pa3.MONTH_ID MONTH_ID,
    pa3.MONTH_DESC MONTH_DESC
from    LU_MONTH    pa3
    join    (LU_REGION    pa2
    join    ((select    a12.MONTH_ID  MONTH_ID,
    a11.REGION_ID  REGION_ID
from    LU_REGION a11 
    cross join    LU_MONTH    a12)    pa1
    join    STATE_REGION_MNTH_SLS    pa0
      on     ((pa1.Region_ID = pa0.REGION_ID
     and pa1.MONTH_ID = pa0.MONTH_ID)))
      on     (pa2.REGION_ID = pa1.Region_ID))
      on     (pa3.MONTH_ID = pa1.MONTH_ID)
group by    pa2.REGION_ID,
    pa2.REGION_NAME,
    pa3.MONTH_ID,
    pa3.MONTH_DESC

After Unified Quoting feature introduced, the query is:

select    sum("pa0"."TOT_DOLLAR_SALES") "Column1",
    "pa2"."REGION_ID" "REGION_ID",
    "pa2"."REGION_NAME" "REGION_NAME",
    "pa3"."MONTH_ID" "MONTH_ID",
    "pa3"."MONTH_DESC" "MONTH_DESC"
from    "LU_MONTH"    "pa3"
    join    ("LU_REGION"    "pa2"
    join    ((select    a12.MONTH_ID  MONTH_ID,
    a11.REGION_ID  REGION_ID
from    LU_REGION a11 
    cross join    LU_MONTH    a12)    pa1
    join    "STATE_REGION_MNTH_SLS"    "pa0"
      on     (("pa1"."Region_ID" = "pa0"."REGION_ID"
     and "pa1"."MONTH_ID" = "pa0"."MONTH_ID")))
      on     ("pa2"."REGION_ID" = "pa1"."Region_ID"))
      on     ("pa3"."MONTH_ID" = "pa1"."MONTH_ID")
group by    "pa2"."REGION_ID",
    "pa2"."REGION_NAME",
    "pa3"."MONTH_ID",
    "pa3"."MONTH_DESC"

However since there is no quotation in the subquery, when the join pass tries to refer to "pa1"."MONTH_ID", instead of pa1.MONTH_ID, warehouse is unable to find the column. Report execution fails with the following error.
 


Status: Execution failed

Error: QueryEngine encountered error: Execute Query failed. 
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [42S22:904: on SQLHANDLE] [Strategy][ODBC Oracle Wire Protocol driver][Oracle]ORA-00904: "pa1"."MONTH_ID": invalid identifier. 

Cause

• When a query is manually inputted (e.g via Type a Query in Web Import, Logic Table View, etc.), Strategy will not perform any modifications on it.
  
• A quoted query that contains an unquoted subquery will fail execution as the database is unable to map between an unquoted identifier and a quoted one. In the error message above, it would be MONTH_ID vs "MONTH_ID"

Action

• Manually edit the queries made with Freeform SQL and put in quotes to avoid this error.

Strategy Internal Reference Number for this technical note is KB483318 and F11007.