Trim on char datatype

[thatiprashant]

Hi Guru's,

I am pretty much new to datastage, currently i am experiencing a probs
where in my source datatype is char(40) when i trim this column and use
for comparing with other varchar column which holds the same data as this,
it is reading the columns as seperate data eventhough they have same values
in both columns.

thanks
prashant

[meena]

Hi...
Can you explain about your job design.....

[ArndW]

Trimming a CHAR(10) field with the value of "hello " will result in a value of "hello " (i.e. unchanged!) if it is being put back into a CHAR(10) field. You need to ensure that your comparison works on identical data types. If you do a transform stage and execute a derivation "IF TRIM(In.CharField)=TRIM(In.VarCharField) THEN 1 ELSE 2" it should work correctly.

[thatiprashant]

Hi...
Can you explain about your job design.....

thanks for the prompt reply.

My design is

database --------> dataset1
char(40) Trim Char(40) prash(data)
COLname Order
database --------> dataset2
varchar(40) varChar(40) prash(data)
Colname order1

lets say my condition is if (order1<>order) then 0 else 1

in normal scenario it shud go to 1 as both has got the same data,but it
is going to 0 considering the data as different


thanks
prashant

[meena]

Hi..
I think you are using a lookup stage..Can you try to do TRIM on the column of dataset2 too...I am not sure whether this is going to work or not but just giving you an option to try..

[ArndW]

Convert your dataset to VarChar(40) and trim your strings.

[thatiprashant]

Convert your dataset to VarChar(40) and trim your strings. ...

thanks Arndw,

with varchar(40) it is working but i was just wondering why it is not working with char(40) is there any particular reason why its not working

[kris007]

Because you cannot perform TRIM function on CHAR datatypes.

[ray.wurlod]

You CAN use Trim on Char data types but, because the target field is Char, it is then automatically padded again with $APT_STRING_PADCHAR characters!

[kris007]

I meant that but should have put it more clearly that CHAR datatype respects the length of the field and pads characters.

[SAMEENA]

Hi everyone,
I have a similar problem.

The database we are using is DB2.

In my job I read data from a table using DB2 API stage and write it in a sequential file.

Now in the table I have a field with char(30).When I insert a record with empty string in this column and after doing a ftp; open the sequential file in wordpad I notice a square shaped character used as pad character.This is I suppose a non printable character.

Now if I change the datatype of the column to varchar,I still notice the character , but only one in numbe and not 30 as in the earlier case.

My problem is when I try to filter this record using len() function it does not get filtered out.Trim() is also of no help.

Please help me out.

thanks in advance.

[ray.wurlod]

Your Char column IS padded, with ^@ (\x00 if you prefer that notation). These are invisible except with a hex editor. The pad character is set using APT_STRING_PADCHAR environment variable.