I am reading data from a fixed lenght record with fixed length columns.
Even though I define each column as Varchar in the input Sequential stage, and as Varchar2 in Oracle target table, I still get trailing blanks.
Since I have so many columns in input, I don't want to explicitly call trim - Shouldn't Varchar take care of this implicitly?
Thanks.
How to get rid of trailing blanks without trim?
Moderators: chulett, rschirm, roy
No.
You will need to trim any data that comes out of a fixed width file. Look into mass adding TRIM to every derivation on a transformer immediately after the source sequential file stage. If you open help on the Transformer, you'll see a column propagation ability, where you can highlight all of the derivations, right click the derivation propagation, and enter something like TRIM($1) which would mass add TRIM(...) around every derivation. That will fix your data coming off the fixed width stage.
I don't know how much you've read on the DSXchange, but we consistently remind people that it's up to you to fix data, that metadata down below really doesn't do anything to the data. You can map a 100K wide text column into a BIT column and there's no issue. It's only when forming fixed width files or talking to databases that the metadata really matters.
You will need to trim any data that comes out of a fixed width file. Look into mass adding TRIM to every derivation on a transformer immediately after the source sequential file stage. If you open help on the Transformer, you'll see a column propagation ability, where you can highlight all of the derivations, right click the derivation propagation, and enter something like TRIM($1) which would mass add TRIM(...) around every derivation. That will fix your data coming off the fixed width stage.
I don't know how much you've read on the DSXchange, but we consistently remind people that it's up to you to fix data, that metadata down below really doesn't do anything to the data. You can map a 100K wide text column into a BIT column and there's no issue. It's only when forming fixed width files or talking to databases that the metadata really matters.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
VARCHAR does not implicitly remove spaces - if you (or someone) puts them there, then they're there. VARCHAR only means that you don't have to fill the maximum width of the field.
Make use of derivation propagation, as Ken suggested.
Make use of derivation propagation, as Ken suggested.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.