whitespace between two words

abha.kalra · Post by **abha.kalra** » Sun Jun 21, 2009 11:06 pm

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.

chulett · Post by **chulett** » Sun Jun 21, 2009 11:13 pm

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?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 22, 2009 7:15 am

Removing trailing spaces is default property for Varchar.

Branch the result immediately after Oracle source and check the result.

chulett · Post by **chulett** » Mon Jun 22, 2009 7:27 am

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)

abha.kalra · Post by **abha.kalra** » Mon Jun 22, 2009 7:55 am

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

Code: Select all

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

miwinter · Post by **miwinter** » Mon Jun 22, 2009 8:03 am

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?

abha.kalra · Post by **abha.kalra** » Mon Jun 22, 2009 8:10 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 22, 2009 8:16 am

What if you get the link from very first transformer without any derivation ?

abha.kalra · Post by **abha.kalra** » Mon Jun 22, 2009 8:18 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 22, 2009 8:21 am

Can you list the derivation of the target column tracing from its source. Something like Craig asked.

abha.kalra · Post by **abha.kalra** » Mon Jun 22, 2009 8:35 am

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.

miwinter · Post by **miwinter** » Mon Jun 22, 2009 8:37 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 22, 2009 9:07 am

Steps B and D are identical but yet said to result in different values.

Are you using any stage variables and derivations in them?

miwinter · Post by **miwinter** » Mon Jun 22, 2009 9:15 am

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'.