Five Stars

Mixed case columns in Oracle fail to analyze

We have the occasional column in Oracle that is mixed case requiring " around the name to preserve case. Data Quality generates invalid SQL for these.  A similar issue in TOS 5.x was reported with a workaround to edit the schema to add the \" around the column name, but this technique fails in DQ.

Edit a schema in the Metadata -> DB Connections -> Table schemas we see the offending column Updated_by
Screenshot from 2018-05-20 15-42-42.png

which generates code like this - note the column name is rolled to upper case:

 

2018-05-20 17:35:22,447 ERROR org.talend.dq.analysis.AnalysisExecutor - Query not executed for indicator: "Duplicate Count". the query is: SELECT COUNT(*) FROM (SELECT "UPDATED_BY", COUNT(*) mycount FROM "PSS"."AUTHAGENCY" m GROUP BY "UPDATED_BY" HAVING COUNT(*) > 1) myquery

 

Wrapping the column name in \"Updated_by\"  in the table schema editor, DQ generates bad SQL and the column name is already rolled to upper case.

2018-05-20 17:47:43,349 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Computing indicator: "Duplicate Count" Executing query: SELECT COUNT(*) FROM (SELECT "\"UPDATED_BY\", COUNT(*) mycount FROM "PSS"."AUTHAGENCY" m GROUP BY "\"UPDATED_BY\" HAVING COUNT(*) > 1) myquery

 

Tags (1)
1 REPLY
Five Stars

Re: Mixed case columns in Oracle fail to analyze