IA coalesce function result

This forum contains ProfileStage posts and now focuses at newer versions Infosphere Information Analyzer.

Moderators: chulett, rschirm

Post Reply
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

IA coalesce function result

Post by qt_ky »

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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
truenorth
Participant
Posts: 139
Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio

Re: IA coalesce function result

Post by truenorth »

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.
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
truenorth
Participant
Posts: 139
Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio

Post by truenorth »

How about trimming first before coalesce?

Try COALESCE(TRIM(column),'') and see if the definition validates.
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
Post Reply