DSXchange

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.

qt_ky wrote: trim(street,'') works OK by itself in IA.

TRIM does not accept a second parameter. Not sure you meant to type the '' inside the function.

trim(coalesce(column,'')) causes some problems 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].

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.

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

Again, COALESCE in the code can potentially return a null and IA does not want to TRIM it.

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.

COALESCE, TRIM and '' all result in NVARCHAR2 data type.

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.

How about trimming first before coalesce?

Try COALESCE(TRIM(column),'') and see if the definition validates.