Trim on char datatype
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 25
- Joined: Mon Sep 19, 2005 11:32 am
Trim on char datatype
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
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
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.
-
- Participant
- Posts: 25
- Joined: Mon Sep 19, 2005 11:32 am
thanks for the prompt reply.meena wrote:Hi...
Can you explain about your job design.....
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
-
- Participant
- Posts: 25
- Joined: Mon Sep 19, 2005 11:32 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.