To trim leading zero's in a sequential file
Moderators: chulett, rschirm, roy
To trim leading zero's in a sequential file
Hi All,
My job design is
ORACLE ENTERPRISE------->TRANSFORMER--------->SEQ FILE
Sequential file has 6 columns. Represented all columns as Varchar(11,2)
First two columns are string values,while rest 4 columns contain amount value. When i load the data in seq file, i need data in format for last 4 columns as eg : 2.17, 666.6, 39901.45, 2890716
But the data in o/p I get is something like below:
00000002.17, 000000666.6, 00039901.45, 000000289
My req is that i need data with trimed leading zero's.
Can anybody suggest me on this?
My job design is
ORACLE ENTERPRISE------->TRANSFORMER--------->SEQ FILE
Sequential file has 6 columns. Represented all columns as Varchar(11,2)
First two columns are string values,while rest 4 columns contain amount value. When i load the data in seq file, i need data in format for last 4 columns as eg : 2.17, 666.6, 39901.45, 2890716
But the data in o/p I get is something like below:
00000002.17, 000000666.6, 00039901.45, 000000289
My req is that i need data with trimed leading zero's.
Can anybody suggest me on this?
To trim leading zero's in a sequential file
go to file properties in the target file and set the column/field properties and its values
Ulab----------------------------------------------------
help, it helps you today or Tomorrow
help, it helps you today or Tomorrow
Re: To trim leading zero's in a sequential file
ulab,
I guess what u are saying is the basic thing that we have to do before loading the data. But the thing is the amount column length varies. So length is mentioned as Varchar(11,2) which i have already mentioned.
I guess what u are saying is the basic thing that we have to do before loading the data. But the thing is the amount column length varies. So length is mentioned as Varchar(11,2) which i have already mentioned.
There's no such thing as Varchar(11,2). Strings don't have a scale.
If you need to trim something, it would make sense to use the trim() function for that, yes? It defaults to space but you can override that and you can also override the default behaviour of "all".
![Confused :?](./images/smilies/icon_confused.gif)
If you need to trim something, it would make sense to use the trim() function for that, yes? It defaults to space but you can override that and you can also override the default behaviour of "all".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
string remove zeros
In String how to define precision and scale.String property have precision only,You can use ltrim (input column).
chullet: 'There's no such thing as Varchar(11,2). Strings don't have a scale.'
Here i meant to say that the metadata defined in the seq file is like :
Columnname SQLtype Length Scale
col1 varchar 11 2
I guess i should have explained it like this instead of Varchar(11,2)
Can I use trim function in sequential file?
Here i meant to say that the metadata defined in the seq file is like :
Columnname SQLtype Length Scale
col1 varchar 11 2
I guess i should have explained it like this instead of Varchar(11,2)
Can I use trim function in sequential file?
You don't specify a scale for a string datatype because there is no scale, as Craig as indicated. Only when you convert it to a numeric datatype (number, decimal, float?) can you specific a scale. While it's still in a string datatype it's just a collection of character data.
Use the trim() function mentioned earlier with the appropriate option to remove leading characters, as documented in the Parallel Job Developer's Guide or on <a href=http://publib.boulder.ibm.com/infocente ... 22%20>this page</a>
Regards,
Use the trim() function mentioned earlier with the appropriate option to remove leading characters, as documented in the Parallel Job Developer's Guide or on <a href=http://publib.boulder.ibm.com/infocente ... 22%20>this page</a>
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
Re: To trim leading zero's in a sequential file
Use CAST in SQL Query level
DS User
DS User
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India