DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2790
Location: USA
Points: 21042

Post Posted: Fri Sep 22, 2017 9:24 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: 11.5.0.2 with Service Pack 2
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



Joined: 18 Jan 2010
Posts: 118
Location: Chicago
Points: 786

Post Posted: Fri Sep 22, 2017 12:46 pm Reply with quote    Back to top    

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.

Quote:

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.

Quote:

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.

Quote:

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
Sr Consultant, Data Governance
Chicago
Rate this response:  
Not yet rated
truenorth
Participant



Joined: 18 Jan 2010
Posts: 118
Location: Chicago
Points: 786

Post Posted: Fri Sep 22, 2017 12:51 pm Reply with quote    Back to top    

How about trimming first before coalesce?

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

_________________
Todd
Sr Consultant, Data Governance
Chicago
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours