Mixed case columns in Oracle fail to analyze

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

Re: Mixed case columns in Oracle fail to analyze

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Enabling Data Governance

Learn how to enable Data Governance

Watch Now

The Definitive Guide to Government Data Quality

Take a peek at the definitive guide to Government Data Quality

Read