Creating a new Data Rule Definition in IA Workbench with nested functions.
Data types are NULLABLE STRING. I need to perform the equivalent of Trim(NullToEmpty(col)) in a DataStage derivation.
coalesce(column,'') works OK by itself in IA.
trim(street,'') works OK by itself in IA.
trim(coalesce(column,'')) causes some problems in IA:
1. Validate fails with error:
Invalid parameter: One of the parameters passed to the scalar function "trim" does not have the expected type.
2. When I try to save the data rule definition, a "Save Failed" error pops up:
com.ascential.investigate.dr.exception.DataRulesException: Exception when saving rule: trim(coalesce(column,'')) unique ......Exception Cause :null
I found that inserting tostring() validates and saves OK.
trim(tostring(coalesce(column,'')))
Why is tostring() necessary? What is the data type of the coalesce() result? I expect it output to be the empty string.
IA coalesce function result
IA coalesce function result
Choose a job you love, and you will never have to work a day in your life. - Confucius
Re: IA coalesce function result
TRIM does not accept a second parameter. Not sure you meant to type the '' inside the function.qt_ky wrote: trim(street,'') works OK by itself in IA.
First, I believe that IA treats '' equal to a null (whether it is a null is debatable, I know). I tested it just now. I created a custom expression that contains the literal '' and in the output, IA displays it as [NULL].trim(coalesce(column,'')) causes some problems in IA:
If so, I am guessing that IA does not like the fact that you are trimming something that may be empty. I know...column may not be null and therefore the result of the COALESCE can be trimmed. But for those values that are null, your COALESCE will return null and IA refuses to TRIM it...in a rule definition. In a data rule, I can code it as a custom expression.
Again, COALESCE in the code can potentially return a null and IA does not want to TRIM it.1. Validate fails with error:
Invalid parameter: One of the parameters passed to the scalar function "trim" does not have the expected type.
2. When I try to save the data rule definition, a "Save Failed" error pops up:
com.ascential.investigate.dr.exception.DataRulesException: Exception when saving rule: trim(coalesce(column,'')) unique ......Exception Cause :null
COALESCE, TRIM and '' all result in NVARCHAR2 data type.I found that inserting tostring() validates and saves OK.
trim(tostring(coalesce(column,'')))
Why is tostring() necessary? What is the data type of the coalesce() result? I expect it output to be the empty string.
So yes, it is strange that you need TOSTRING to work around the problem. And yes, it is strange that TRIM without TOSTRING causes the error.
Sorry I am not much help. I just know from experience that IA is quirky with many things including COALESCE, TRIM and its treatment of nulls.
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
Sr Consultant, Data Quality
San Antonio TX