Page 1 of 1
whitespace between two words
Posted: Sun Jun 21, 2009 11:06 pm
by abha.kalra
HI,
I am getting extra white space between 2 words - address column
from oracle and inserting it into teradata.
exmple
cold ice land
cold ice land ( extra space)
my requirement is to insert the row with extra space as it is. while inserting the in to teradata I am applyin function trim(colname,'B')
Also I have 3 transformer stage in my job.
For some reason the these extra space are getting removed automatically before applyin trim function.
datatype of the column is varchar,
Please help how can I insert this text as it is.
Posted: Sun Jun 21, 2009 11:13 pm
by chulett
It will go into the target intact unless you explicitly remove them. How are you certain that they are gone before you send them to Teradata? Have you added a logging link (or three) to check?
Posted: Mon Jun 22, 2009 7:15 am
by Sainath.Srinivasan
Removing trailing spaces is default property for Varchar.
Branch the result immediately after Oracle source and check the result.
Posted: Mon Jun 22, 2009 7:27 am
by chulett
FYI - a "trick" I use - you need to start a 'reply with quote' session to take a proper look at the example. Without code tags, all extra spaces are always removed by the forum software. Then you'll see it's not about
trailing spaces but rather
internal spaces.
Code: Select all
cold ice land
cold ice land ( extra space)
Posted: Mon Jun 22, 2009 7:55 am
by abha.kalra
chulett wrote:It will go into the target intact unless you explicitly remove them. How are you certain that they are gone before you send them to Teradata? Have you added a logging link (or three) to check? ...
This is not happening. I have added a flat file with the teraApi stage and passing the output in both the stages. In the flat file derivation, there is no trim function .
example
lkname.columnname.
upon viewing the output- both the address lines are coming as
ice cold land
ice cold land
However it should be
ice cold land
I am using orace function Upper in the select query for this field and I not applying any function to any of the previous transformer.
Please suggest
Posted: Mon Jun 22, 2009 8:03 am
by miwinter
Is this a server or PX job? You cite server in your post yet it is posted in the PX forum...
Does the varchar field concerned have a length defined?
Posted: Mon Jun 22, 2009 8:10 am
by abha.kalra
miwinter wrote:Is this a server or PX job? You cite server in your post yet it is posted in the PX forum...
Does the varchar field concerned have a length defined?
This is a server job . Sorry for confusion. I am not sure how to change it now.
There are two field which has same issue. the datatype and the length are:
1. varchar 100
2. varchar 60
Posted: Mon Jun 22, 2009 8:16 am
by Sainath.Srinivasan
What if you get the link from very first transformer without any derivation ?
Posted: Mon Jun 22, 2009 8:18 am
by abha.kalra
Sainath.Srinivasan wrote:What if you get the link from very first transformer without any derivation ? ...
I have added a flat file in very first transformer and without any function on the derivation( linkname.colname) . In that flat file the data is coming intact
i.e I can see the extraspace. But when the data reach to the final transformer the spaces are gone.
Posted: Mon Jun 22, 2009 8:21 am
by Sainath.Srinivasan
Can you list the derivation of the target column tracing from its source. Something like Craig asked.
Posted: Mon Jun 22, 2009 8:35 am
by abha.kalra
Sainath.Srinivasan wrote:Can you list the derivation of the target column tracing from its source. Something like Craig asked. ...
A.Transformer 1:
linkname.columnname varchar 50
B.transformer 2:
linkname.columnname varchar 50
C.Transformer 3: to teraApi
Trim(linkname.columnname,'B') varchar 100( for teraApi)
D.Transformer 3: to flatfile
linkname.columnname varchar 50
Both C and D are resuting in same values.
Posted: Mon Jun 22, 2009 8:37 am
by miwinter
Definitely some change going on in the subsequent transforms I'd suspect. I've happily gone from char to varchar and back to char in a DB2 target from a flat file, retaining space in between three words as you have shown you require. That or some quirk in Teradata.
Posted: Mon Jun 22, 2009 9:07 am
by Sainath.Srinivasan
Steps B and D are identical but yet said to result in different values.
Are you using any stage variables and derivations in them?
Posted: Mon Jun 22, 2009 9:15 am
by miwinter
Furthemore, you stated:
I am using orace function Upper in the select query for this field and I not applying any function to any of the previous transformer
... so I'm wondering why you have those transformers in place at all? It's superfluous overhead and clearly seems to be the basis of the 'fault'.