Mixed case columns in Oracle fail to analyze

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)
Highlighted
Five Stars

Re: Mixed case columns in Oracle fail to analyze

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.