You already found the bad value, '00' :-)
See
http://forums.teradata.com/forum/database/comparing-2-different-datatypes-giving-undesired-results#comment-141218
Dieter
Thanks dnoeth !! So '00'xc is a special DB2 value (or no value).
Here is my actual problem with '00'xc value. This col XXX is part of large concatenated string that is passed to HASH_MD5 to get the checksum of the row. The checksum function is ignoring all field values after the col XXX. It gives the same checksum for changes after this column. Its like checksum is treating this value as the end of the string.
I can use the oTranslate function to replace this one value '00'xc but I am not sure how many more such values i need to handle.
HASH_MD5 is a C-function and C treats a binary zero as string terminator.
You should fix the current data and the apply oTranslate during load.
Dieter
I need to mask the data as it has sensitive information. I tried using MD5 c-function. I am not sure how it works and when I ran the bteq script in MD5 function, it is giving me some error. Could any one please help me to understand the MD5 function or possibly any other solution to mask the data would also serve. Thanks in advance
Hi There,
We have a column say XXX as CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
Data from a source DB2 system has been loaded in this column., when i query it it just shows as spaces in SQLA. But I am not able to trim OR apply a NULLIF(XXX,'') on this column, i.e. it has some value in it that is not space. How do i find the value and in which charset is it ?
If I do a CHAR2HEXINT(XXX) I get 0000. So i am not very clear on what is the value in this column.