Data truncation

mcs_suman · Post by **mcs_suman** » Wed Feb 09, 2011 12:24 am

Hi,

I have a field named material in my database which is of length 18.But the max length of the value is 7.I am using the below query and specified the column length as 12 because I am joining with similar column of length 12.But i am getting the below warning..
If I set it as 18 as in Database then I have issues in joining as the similar couln to join has length 12.
Can any one help me with this.

Query used:

select substr(MATERIAL,1,max(length(ltrim(rtrim(MATERIAL)))))as MATERIAL from A GROUP BY MATERIAL

Warning:

The size specified for column 'MATERIAL' (12) is less than that of the source table column (18);data truncation may result.

richdhan · Post by **richdhan** » Wed Feb 09, 2011 1:32 am

Hi,

If you want to avoid these warnings, you can do the substring functionality in a transformer and then do a join.

HTH
--Rich

mcs_suman · Post by **mcs_suman** » Thu Feb 10, 2011 11:08 am

Adding transformer downstream we can avoid warning..My question is if we fetch a value using sub string from a column we will specify only the desired length of the sub string and not the length of the source column...
In such case we get warning to use the Length of the source column.
Is it a bug with tool?
Regards
Suman

ray.wurlod · Post by **ray.wurlod** » Thu Feb 10, 2011 3:18 pm

It's not a bug - it's an alert. As the metadata are specified there exists a potential danger that you would lose data through truncation, so an alert (warning) message to this effect is logged.