APT_ORACLE_PRESERVE_BLANKS equivalent in Teradata

prashanth.spl · Post by **prashanth.spl** » Mon Jan 03, 2011 11:22 am

Hi,

Is there any equivalent variable as APT_ORACLE_PRESERVE_BLANKS for Teradata database?

Thanks,
Prashanth

varaprasad · Post by **varaprasad** » Mon Jan 03, 2011 1:37 pm

There is no equivalent available for Teradata. May be, not required.

In case of Oracle, the APT_ORACLE_PRESERVE_BLANKS variable affects the whitespace trimming. It is important in case of SQL*Loader, because Oracle removes the spaces and considers fields with only spaces to be NULL values.
But with Teradata, it is different. Teradata by default removes the trailing spaces for CHAR datatypes which doesn't really matter if the column definition has required length defined. Apart from that it doesn't really botch up anything.

So you have to determine why you need this.

prashanth.spl · Post by **prashanth.spl** » Mon Jan 03, 2011 8:05 pm

I am trying to load the Teradata Table using a dataset which contains the Varchar datatypes.

In table also i have all varchar datatypes.

For Example

Table: col1 varchar(10);

Dataset: col1: 'prashanth '

While loading i want to ignore the last spaces and load to the table.

How can we achieve this?

Thanks,
Prashanth

chulett · Post by **chulett** » Mon Jan 03, 2011 9:49 pm

What do you mean by "ignore the last spaces"?

I'm guessing from your subject that you want to preserve the trailing space and it gets removed during the load?

prashanth.spl · Post by **prashanth.spl** » Mon Jan 03, 2011 10:01 pm

chulett,

Yes your right. spaces should be removed during load.

The data 'prashanth ' should load to the table as 'prashanth'.

How can we do this?

chulett · Post by **chulett** » Mon Jan 03, 2011 10:17 pm

So actually I'm not right and the exact opposite of what I said is what you need.

Trim() or TrimB()

prashanth.spl · Post by **prashanth.spl** » Mon Jan 03, 2011 10:21 pm

chulett,

There hundrends of jobs. For each field, if we want to transfrom, then it will take so much of time. is there any similar variable as APT_ORACLE_PRESERVE_BLANKS?

chulett · Post by **chulett** » Mon Jan 03, 2011 10:29 pm

Not that I am aware of... and besides, you don't want to "preserve blanks". Perhaps some Teradata experts need to weigh in here but I'd wager it works like any other database - if you send the field over with spaces in it, that's what you get. If you don't want them you need to trim them before they get that far.

AFAIK, it's only Oracle that plays this crazy automagic trimming game and decides that a field with only spaces in it is 'empty' and that an empty field is the same as a null field. Hence the special Oracle environment variable to shut that off.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 03, 2011 11:42 pm

Learn about "derivation substitution" in the Transformer stage - you can save yourself a lot of work.