Leading 0's
Moderators: chulett, rschirm, roy
Leading 0's
Hi there I have a Numeric value in a job that I am trying to trim just the leading zeros for example:
Field1 [Numeric 17,2] = 000000000000000.00
The result I would like would be 0.00
Any ideas?
Thanks
Field1 [Numeric 17,2] = 000000000000000.00
The result I would like would be 0.00
Any ideas?
Thanks
Thanks in advance,
Check out this post
viewtopic.php?t=101418
viewtopic.php?t=101418
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
DSguru2B wrote:Check out this post
viewtopic.php?t=101418
Thanks DS_Guru:
I used the TRIM(COL_NAME,'0','L') removes the leading zeros but then it removes the 0's from the decimal place too.
Example: 00012345.00
Name SQL TYPE LENGTH SCALE
COL_NAME Num 15 2
when I apply TRIM(COL_NAME,'0','L')
I get 12345
I want 12345.00
Any idea...went thru the forum and people talk about Decimal to String format, floors and fab.
Thanks in advance,
-
- Participant
- Posts: 47
- Joined: Fri Sep 23, 2005 6:01 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That generates a random number - not, I suspect, what the OP desires!
In server jobs you can simply perform arithmetic (for example add zero to the number) and it returns the shortest possible string. You could then format it to have two decimal places.
In parallel jobs, it is not quite so easy. You can convert the decimal number to a string, use the Field() function to get that part of the string to the left of the decimal place, trim non-significant leading zeroes from that, concatenate the decimal placeholder and its following two digits then convert the resultant string back to decimal.
View Data will, of course, always display the leading zeroes whether they are there or not; that's its way of reporting the precision and scale to you.
In server jobs you can simply perform arithmetic (for example add zero to the number) and it returns the shortest possible string. You could then format it to have two decimal places.
In parallel jobs, it is not quite so easy. You can convert the decimal number to a string, use the Field() function to get that part of the string to the left of the decimal place, trim non-significant leading zeroes from that, concatenate the decimal placeholder and its following two digits then convert the resultant string back to decimal.
View Data will, of course, always display the leading zeroes whether they are there or not; that's its way of reporting the precision and scale to you.
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.
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hello ray.wurlod:ray.wurlod wrote:When you format it to two decimal places it regains its leading zero. ...
Would really appreciate if you could please tell me how to do this in Parallel job. Cause unable to find anything for format under parjdev.pdf or advpx.pdf. The Basic.pdf mentions the fmt() function but am unable to apply it.
Thanks in advance,
Could anyone point out what is wrong with this:
(TRIM(FMT(COL_NAME,"R2"),'0','L')
Example: 00012345.00
Name SQL TYPE LENGTH SCALE
COL_NAME Num 15 2
when I apply TRIM(COL_NAME,'0','L')
I get 12345
I want 12345.00
so use FMT() Function to keep the 2 places of the decimal. However, PX is not accepting the above cmd.
(TRIM(FMT(COL_NAME,"R2"),'0','L')
Example: 00012345.00
Name SQL TYPE LENGTH SCALE
COL_NAME Num 15 2
when I apply TRIM(COL_NAME,'0','L')
I get 12345
I want 12345.00
so use FMT() Function to keep the 2 places of the decimal. However, PX is not accepting the above cmd.
Thanks in advance,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You will find the BASIC Transformer stage in the Stage Types branch of the Repository. It was intended not to put it on the Palette by default, though you can of course customize your Palette.
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.
Thank you ray.wurlod and DSguru2B....was able to add the Basic Transformer from the Stage Types using the customized Palette.ray.wurlod wrote:You will find the BASIC Transformer stage in the Stage Types branch of the Repository. It was intended not to put it on the Palette by default, though you can of course customize your Palette. ...
Using : TRIM(Fmt(out_fa003_lsmw.PAR_AMOUNT,"R2"),'0','L') in basic transformer am able to get the result
12345.00
However if the input data is 0000000.00
its returning .00
how do I get 0.00...?
Thanks in advance,