Talend DQ Pattern Frequency Analysis fails on Numeric data type

DL001 — Wed, 07 Dec 2016 15:42:07 GMT

I am profiling a NUMERIC(23,0) column - the current SQL generated for analysing the Low Pattern Frequency Analysis truncates when it does a CAST( <column> AS CHAR(20)) and therefore causes the analysis to fail.

Re: Talend DQ Pattern Frequency Analysis fails on Numeric data type

Anonymous — Tue, 20 Dec 2016 02:07:49 GMT

Hi
I am not able to reproduce the the issue on version 6.2.1, can you please give us some example data to explain your problem?
Regards
Shong

Re: Talend DQ Pattern Frequency Analysis fails on Numeric data type

DL001 — Tue, 20 Dec 2016 12:52:27 GMT

i can't give you data I am using.
The problem can easily be reproduced:
- create a table with some columns, one of which is a NUMERIC( 23, 0)
- create an analysis on this table, including the NUMERIC column
It will fail and you will see the following in the log:
!ENTRY org.talend.platform.logging 1 0 2016-12-20 12:50:07.928
!MESSAGE 2016-12-20 12:50:07,926 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Computing indicator: "Pattern Frequency" Executing query: SELECT TOP 10 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( CAST("NumericColumn" AS CHAR(20)) ,'a','a'),'b','a'),'c','a'),'d','a'),'e','a'),'f','a'),'g','a'),'h','a'),'i','a'),'j','a'),'k','a'),'l','a'),'m','a'),'n','a'),'o','a'),'p','a'),'q','a'),'r','a'),'s','a'),'t','a'),'u','a'),'v','a'),'w','a'),'x','a'),'y','a'),'z','a'),'A','A'),'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A'),'L','A'),'M','A'),'N','A'),'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A'),'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A'),'0','9'),'1','9'),'2','9'),'3','9'),'4','9'),'5','9'),'6','9'),'7','9'),'8','9'),'9','9') , COUNT(*) c FROM "Database"."dbo"."Table" t GROUP BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( CAST("NumericColumn" AS CHAR(20)) ,'a','a'),'b','a'),'c','a'),'d','a'),'e','a'),'f','a'),'g','a'),'h','a'),'i','a'),'j','a'),'k','a'),'l','a'),'m','a'),'n','a'),'o','a'),'p','a'),'q','a'),'r','a'),'s','a'),'t','a'),'u','a'),'v','a'),'w','a'),'x','a'),'y','a'),'z','a'),'A','A'),'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A'),'L','A'),'M','A'),'N','A'),'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A'),'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A'),'0','9'),'1','9'),'2','9'),'3','9'),'4','9'),'5','9'),'6','9'),'7','9'),'8','9'),'9','9') ORDER BY c DESC

!ENTRY org.talend.platform.logging 4 0 2016-12-20 12:50:07.937
!MESSAGE 2016-12-20 12:50:07,932 ERROR org.talend.dq.analysis.ColumnAnalysisSqlParallelExecutor - java.sql.DataTruncation: Data truncation

topic Re: Talend DQ Pattern Frequency Analysis fails on Numeric data type in Data Quality

Talend DQ Pattern Frequency Analysis fails on Numeric data type

Re: Talend DQ Pattern Frequency Analysis fails on Numeric data type

Re: Talend DQ Pattern Frequency Analysis fails on Numeric data type