DSXchange

Hi,
A dataset is created using the $APT_STRING_PADCHAr(0x20).
when inserting to a table, the data gets inserted along with the space.
Use of trim function has not so far helped to remove the space inspite of the data type in oracle being a varchar.
Is there any other means of doing this?

Pls let me know.
Thanks in advance!!

The trim function will always remove spaces from strings, so either you don't have spaces but other characters or you don't have strings - note, you cannot remove trailing spaces from a CHAR field, just from VARCHAR. What are your datatypes in the job and the table? Are you doing a TRIM() on CHAR field and later implicilty converting to VARCHAR?

I have a dataset ->column import stage-> oracle OCI stage.
I added the transfomer stage btween the col import and oci stage and tried performing the trim.
The datatype in table is varchar.

What is the datatype from your dataset schema? Add a 2nd output link to your transform going into a peek stage with your TRIM() string and add a column of LEN(trimmed-string) and see if the results are as expected.

The data type from the dataset is char, and the results are as expected.
The trim(string) and the len(trim(string) are the same.
The actual len(string) is 9 but the data that has gone into the table is Len(15).

You cannot TRIM a char field. It has fixed width and will always blank pad to the fixed length. You need to declare a VARCHAR column datatype and then TRIM your original CHAR column into that.

Pedantically you can TRIM a CHAR data type, and it is immedately padded again with APT_STRING_PADCHAR (automatically, because its data type is CHAR).

The effect is the same.

That's why am using the transformer stage in between where I specify the RHS column as a varchar. Am I missing something here?

This is the first mention of a varchar RHS column. If you explicitly TRIM that column and output it to a peek stage are the spaces gone? And then they re-appear when writing to your database? What database are you using?

Am using the Siebel database...

Siebel is the CRM system sitting on top of a database - probably DB2 or Oracle. And what happens in the PEEK stage with the trimmed field?

ya, its the oracle...and the peek stage shows trimmed fields.

So the RHS column is trimmed correctly, and that is what you are passing on to your output stage where the blanks are being added in again? This would only happen if you are going to a fixed width CHAR field - are you sure you aren't doing that somewhere? Are you using user-defined SQL and, if so, are you doing any conversions there?

The target field is a varchar field but yes! ....am using a user defined Insert statement and am not trimming there. So this should be it. Lemme try it out trimming in the query as well.
Thanks.