Text Statistic Column Analysis on Netezza Table

[Anonymous]

Hello,
Talend Open Studio for Data Quality (5.4.1.r111943)
Using nzjdbc.jar to create connection to Netezza NPS 7.0 P-3 Build 27812
I've created a column analysis, selected 1 column (varchar data type), and selected the Minimal Length Text Statistic indicator. I receive the following error when I try and run this analysis (error copy and pasted from log file).
It appears that the query is trying to use ifnull, which I confirmed is not working on Netezza. If I change the query in my query directly to Netezza to use isnull, then the query works.
I also confirmed if I try any of the indicators under Simple Statistics, they all work. I am only having trouble with the Text Statistic indicators which are using the ifnull function in their query.

------------------------------------------------------------------------------------------------------------------------------------
!ENTRY org.talend.platform.logging 1 0 2014-01-13 16:24:42.417
!MESSAGE 2014-01-13 16:24:42,417 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Executing query: SELECT MIN(CHAR_LENGTH("CM_DESC")) FROM "CVADMSBP"."ADMIN"."NOC_DOCSIS_INVENTORY_ATTR" WHERE ("CM_DESC" IS NOT NULL ) AND (TRIM(IFNULL("CM_DESC",'NULL TALEND')) <> '' )

!ENTRY org.talend.platform.logging 2 0 2014-01-13 16:24:42.420
!MESSAGE 2014-01-13 16:24:42,420 WARN org.talend.dq.analysis.ColumnAnalysisSqlExecutor - org.netezza.error.NzSQLException: ERROR: Function 'IFNULL(VARCHAR, UNKNOWN)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

!STACK 0
org.netezza.error.NzSQLException: ERROR: Function 'IFNULL(VARCHAR, UNKNOWN)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:276)
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
at org.netezza.sql.NzConnection.execute(NzConnection.java:2639)
at org.netezza.sql.NzStatement._execute(NzStatement.java:848)
at org.netezza.sql.NzStatement.execute(NzStatement.java:305)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.ColumnAnalysisSqlParallelExecutor.run(Unknown Source)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor$ExecutiveAnalysisJob.run(Unknown Source)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
!ENTRY org.talend.platform.logging 4 0 2014-01-13 16:24:42.421
!MESSAGE 2014-01-13 16:24:42,421 ERROR org.talend.dq.analysis.AnalysisExecutor - Query not executed for indicator: "Minimal Length" SQL query: SELECT MIN(CHAR_LENGTH("CM_DESC")) FROM "CVADMSBP"."ADMIN"."NOC_DOCSIS_INVENTORY_ATTR" WHERE ("CM_DESC" IS NOT NULL ) AND (TRIM(IFNULL("CM_DESC",'NULL TALEND')) <> '' )

!ENTRY org.talend.platform.logging 4 0 2014-01-13 16:24:42.622
!MESSAGE 2014-01-13 16:24:42,621 ERROR org.talend.dq.analysis.AnalysisExecutor - At least one analysis execution failed, Check the error logs for more details!

[Sebastiao_Qlik]

Hi,
Does it happen with "Minimal Length Indicator"?
The default query does not contain the IFNULL function.
But it may indeed happen with the "Minimal Length with Null" and "Minimal Length with Blank and null' indicators.
How to fix it:
- fast way: modify the *default* query template in Libraries/System indicators/Text Statistics/
- the cleanest way (a bit longer):
1. create a JDBC connection with the Netezza driver
2. edit the template of the Libraries/System indicators/Text Statistics/"Minimal Length with Null"
a. click on "plus" button to add a new indicator definition
b. select "Netezza" in the dropdown list
c. set the correct template according to Netezza syntax.

[Anonymous]

Thank you scorreia. I used the second option in your response (the cleanest way) and that worked. I created the additional additional indicators using the Netezza option and had no problem.
FYI, it does happen with the Minimal or Maximal Length indicator as there is an ifnull condition in the where clause of the default template for these indicators. Creating a Netezza template and changing the ifnull function to isnull resolved my issue.
Thanks again,
Chris

[Sebastiao_Qlik]

Thanks Chris.
If you have time, it would be nice to list all templates that require an adaptation for Netezza, and provide the correct template. You may provide this information in our issue tracker: https://jira.talendforge.org
If you can do this, I'll make sure that these changes will be included in the next release.
Thanks.