Transformer compilation error

[hsahay]

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

[ray.wurlod]

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:

Code: Select all

If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : TrimLeadingTrailing(NullToValue(lnk1.C, param_DefaultValue))

s link1.B nullable? If so, you have some more work to do.

[hsahay]

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.

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:

Code: Select all

If IsNull(lnk1.A) Then param_DefaultValue Else lnk1.A : ' - ' : lnk1.B : ' - ' : TrimLeadingTrailing(NullToValue(lnk1.C, param_DefaultValue))

s link1.B nullable? If so, you have some more work to do.

[ray.wurlod]

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.

[hsahay]

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.