How to stop default type conversion in MODIFY stage
Posted: Thu Jan 11, 2018 8:15 am
Hi,
I have generic job where I am handling NULL and performing TRIM for string type of column.
As output column of these, If I do not assign data length for output then by default it increases the length of the column. Example:
CASE1:
---------
Output_Col:string=handle_null(in_col,' ')
Output_Col:string=string_trim(in_col)
In source in_col is defined as VARCHAR(20) but after these conversion it increases the length to maximum limit of VARCHAR and since in target it is defined as VARCHAR(20) so job fails because of increased length.
I could define these conversions as below to take care of this issue, when I define the rule as below, I do not get above error & job works fine.
CASE2:
--------
Output_Col:string[20]=handle_null(in_col,' ')
Output_Col:string[20]=string_trim(in_col)
But it might change the value of the 'in_col' with in 'Output_Col'.
I can say this because when I generate CHECKSUM on 'Output_Col' then I get different value in CASE1 and CASE2.
Queries:
1. Is there any way I handle this and datatype length does not change after these conversions (as in CASE1)?
2. I came to know there is a 'environment' variable which stops MODIFY operator to change the datatype or length, but I am not able to find it. Any Idea on that variable?
Any help in this would be great help. Thanks.
I have generic job where I am handling NULL and performing TRIM for string type of column.
As output column of these, If I do not assign data length for output then by default it increases the length of the column. Example:
CASE1:
---------
Output_Col:string=handle_null(in_col,' ')
Output_Col:string=string_trim(in_col)
In source in_col is defined as VARCHAR(20) but after these conversion it increases the length to maximum limit of VARCHAR and since in target it is defined as VARCHAR(20) so job fails because of increased length.
I could define these conversions as below to take care of this issue, when I define the rule as below, I do not get above error & job works fine.
CASE2:
--------
Output_Col:string[20]=handle_null(in_col,' ')
Output_Col:string[20]=string_trim(in_col)
But it might change the value of the 'in_col' with in 'Output_Col'.
I can say this because when I generate CHECKSUM on 'Output_Col' then I get different value in CASE1 and CASE2.
Queries:
1. Is there any way I handle this and datatype length does not change after these conversions (as in CASE1)?
2. I came to know there is a 'environment' variable which stops MODIFY operator to change the datatype or length, but I am not able to find it. Any Idea on that variable?
Any help in this would be great help. Thanks.