Trim function - Extra space in output

ds_search2008 · Post by **ds_search2008** » Mon Dec 22, 2008 11:51 am

I'm using @INROWNUM funtion in one of the columns and rest other column derivations are included with Trim functon in the transformer stage prior to DB2 UDB API output stage. The output table has all the columns defined as NOT NULL. The job is working fine, except that extra spaces are getting inserted into output columns in DB2 table.

The expected output for example shoule be

Col1|Col2|col3|col4|col5

100|1|10|count|189.39|Z
100|2|130|num|37|Z

However actual output that I'm getting is

Col1|Col2|col3|col4|col5

100|1|10|count|189.39|Z
100|2|130|num|37|Z
100|3| | | |Z
100|4| | | |Z
100|5| | | |Z

I don't understand from where the extra spaces are getting inserted(from 3rd row for instance). Can you please share your views on this issue.

kandyshandy · Post by **kandyshandy** » Mon Dec 22, 2008 12:13 pm

Check whether there is a whitespace coming from source. For e.g. in people soft systems, they use whitespace when a column has blank. Trim of whitespace will still be a whitespace. So you may want to look for other trim options

or

simply use

If Trim(column) = ' ' Then '' Else Trim(column)

chulett · Post by **chulett** » Mon Dec 22, 2008 12:25 pm

What are the data types in your job for the fields in question?

ds_search2008 · Post by **ds_search2008** » Mon Dec 22, 2008 11:23 pm

Thanks a lot for your time and replies.

If Trim(column) = ' ' Then '' Else Trim(column)

I have tried the above option. However, I'm still getting the whitespaces.

What are the data types in your job for the fields in question?

Two output fields are defined as character datatype - length(20)
One other field is defined as smallInt - length (2)

Whitespaces are getting inserted into these three fields mentioned above. Can you please help me to fix this issue.

chulett · Post by **chulett** » Mon Dec 22, 2008 11:46 pm

You can't trim a character field, they would need to be Varchars for that to work.

ds_search2008 · Post by **ds_search2008** » Tue Dec 23, 2008 12:15 am

Thanks a lot Craig.

I found the solution for this bug. I'm using the following derivation (checking for white spaces) in one of key columns say col6.

If (DSLink1.col3 <> ' ') AND (DSLink2.col4 <> ' ') AND (DSLink3.col5 <> ' ' ) Then @INROWNUM Else 1

The above columns (col3, col4 and col5) are defined as NOT NULL in DB2 and col6 is a composite (primary) key column which would not allow duplicate values. Therefore I'm able to achieve the desired output.

Col1|Col2|col3|col4|col5|col6

100|A|10|count|189.39|Z|1
100|B|130|num|37|Z|2

Pls correct me if I'm wrong. If this is correct then I can close this topic as "Resolved".

Many Thanks