Page 1 of 2
how to avoid warngs when convert from varchar(10) to char(4)
Posted: Sun Oct 15, 2006 2:18 pm
by 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.
Posted: Sun Oct 15, 2006 2:46 pm
by chulett
Ensure that you don't send more than four characters to the target field.
Posted: Sun Oct 15, 2006 3:02 pm
by 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.
Posted: Sun Oct 15, 2006 6:45 pm
by ray.wurlod
You can achieve this by truncating it earlier in the job, using probably a Modify stage or a Transformer stage.
Posted: Sun Oct 15, 2006 6:49 pm
by 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.
thanks all for the replies
Posted: Sun Oct 15, 2006 7:44 pm
by 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
Posted: Sun Oct 15, 2006 8:56 pm
by 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.
Posted: Sun Oct 15, 2006 9:12 pm
by rparimi
thank you.... ithink that is more efficient than using a transformer
Posted: Tue Oct 17, 2006 4:02 pm
by 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.
Posted: Tue Oct 17, 2006 7:35 pm
by 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.
I am still getting warning message
Posted: Wed Oct 18, 2006 7:23 am
by 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.
Posted: Wed Oct 18, 2006 7:16 pm
by kumar_s
Try to trun off the RCP.
Posted: Wed Oct 18, 2006 7:29 pm
by 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.
Posted: Thu Oct 19, 2006 9:21 am
by Meera
Hi
Try this
targetcolumn:string[4] =Handle_null(string_trim(source_column),"xxxx")
It works for me
that works..
Posted: Thu Oct 19, 2006 12:56 pm
by rparimi
Hi Meera..
this worked for me.. thanks for resolving the issue.
thanks everyone for their input and suggestion.