I am trying to write the below derivation in the transformer for one of the field
If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : NullToValue(Trimleadingtrailing(lnk1.C), param_DefaultValue) where
lnk1---link
A,B,C--input columns
Param_DefaultValue---job parameter
When i am compiling the job, i am getting the error which shows a message "Parameter specified but not used in flow"
When i tried above derivation by removing trim as shown below, job is getting compiled.
If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : NullToValue(lnk1.C, param_DefaultValue)
Am i going wrong with trim? please help me in this.
Thanks in advance
Transformer compilation error
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Nothing's "going wrong" with Trim function - but you aren't preventing null values of lnk1.C from getting to it. Functions are intolerant of null. Try this:
s link1.B nullable? If so, you have some more work to do.
Code: Select all
If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : TrimLeadingTrailing(NullToValue(lnk1.C, param_DefaultValue))
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ray, Thanks for the response
It is working but i have one doubt in using this.
we are getting data in column C with only spaces in them, so if i use the function as said then firstly spaces will not be considered as nulls, so those values will not be replaced with default parameter which is not correct according to our requirement.
if we trim them before checking for nulls then those spaces will be trimmed and so the value will be considered as null and hence it will be replaced with default value.
Am i correct? please correct me if i am wrong.
It is working but i have one doubt in using this.
we are getting data in column C with only spaces in them, so if i use the function as said then firstly spaces will not be considered as nulls, so those values will not be replaced with default parameter which is not correct according to our requirement.
if we trim them before checking for nulls then those spaces will be trimmed and so the value will be considered as null and hence it will be replaced with default value.
Am i correct? please correct me if i am wrong.
ray.wurlod wrote:Nothing's "going wrong" with Trim function - but you aren't preventing null values of lnk1.C from getting to it. Functions are intolerant of null. Try this:s link1.B nullable? If so, you have some more work to do.Code: Select all
If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : TrimLeadingTrailing(NullToValue(lnk1.C, param_DefaultValue))
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can't trim before checking for nulls, because the trim functions are intolerant of nulls (this is eased in the next version by the way, because everyone hates it). You just need to re-think the logic. You probably need a two-stage If..Then..Else construct.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ray, Thanks for the response.
I have resolved it by using the expression as below.
If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : if nulltoempty(lnk1.C)="" then param_DefaultValue else Trimleadingtrailing(lnk1.C)
Now job got compiled and result is as per the requirement.
I am marking this thread as resolved.
I have resolved it by using the expression as below.
If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : if nulltoempty(lnk1.C)="" then param_DefaultValue else Trimleadingtrailing(lnk1.C)
Now job got compiled and result is as per the requirement.
I am marking this thread as resolved.