how to avoid warngs when convert from varchar(10) to char(4)

[rparimi]

I am using a modify stage and in specification i specified

target_column:string = source_column

where target_column is specified as CHAR(4) and source column is defined as Varchar(10)

I am getting warning with possible trucation. how can i avoid this warning message.

[chulett]

Ensure that you don't send more than four characters to the target field.

[ArndW]

What Craig has stated is that in order to avoid real data truncation your source column cannot contain more than 4 characters. To avoid the warning your datatype's metadata needs to be 4 or less characters as well.

[ray.wurlod]

You can achieve this by truncating it earlier in the job, using probably a Modify stage or a Transformer stage.

[kumar_s]

Perhaps using the same Transofrmer stage to fetch first 4 char(may be after Trim()) and map to Char(4). If its sure that, the input may not contain more that 4 char.

[rparimi]

thanks every one for their reply. i can use Left() function to get the required four characters from the source_column. is there any other way of doing the same... without using Left/Right functions

[ray.wurlod]

If that's all the transformation you need to do, prefer a Modify stage (it's far more light weight). Use the substring function in that stage.

[rparimi]

thank you.... ithink that is more efficient than using a transformer

[rparimi]

Hi Ray,
I used a substring function and in the specification, i included

Code: Select all

target_column:string[4]=Handle_Null(substring[0,4](source_column),"").

though the field is converted from length(10) to length(4), i am getting a warning message

Modify_135: When checking operator: When binding output schema variable "outRec": When binding output interface field "target_column" to field "source_column": Conversion from source type "string[max=10]" to result type "string[max=10]": Taking substring of variable-length source string.

[ray.wurlod]

Your replacement value for null does not contain four characters. It must, if it's destined for a string[4] field.

Irrespective of that, Modify is very verbose about what it's doing. Create a message handler to demote this warning to an informational message.

[rparimi]

I am still getting warning messages though i converted the replacement string to 4 characters.. the specification looks like

Code: Select all

target_column:string[4]=Handle_Null(substring[0,4](source_column),"xxxx").

I am still getting the same warning message. Please suggest if there is any alternative

Modify_135: When checking operator: When binding output schema variable "outRec": When binding output interface field "target_column" to field "source_column": Conversion from source type "string[max=10]" to result type "string[max=4]": Taking substring of variable-length source string.

[kumar_s]

Try to trun off the RCP.

[vmcburney]

I hate to say it and it probably makes me a lazy programmer but this is why I don't spend a lot of time trying to get warning messages to go away. You can lose days on it and the behaviour of the job does not change one whit. Go for the message handler if you are sure the warning is not harming your job and it has been accepted by your testers.

[Meera]

Hi

Try this

targetcolumn:string[4] =Handle_null(string_trim(source_column),"xxxx")

It works for me

[rparimi]

Hi Meera..
this worked for me.. thanks for resolving the issue.

thanks everyone for their input and suggestion.