Folks,
I have a Decimal(11,2) amount field, which I want to add thousand-separator to and insert to a target Varchar2 field.
For e.g. 25000.50 should be 25,000.50.
What function could be used?
Thx.
How to add thousand-separator comma's to decimal field
Moderators: chulett, rschirm, roy
Welcome aboard. I assume you're going to need to deal with millions as well. I should imagine that a conversion to VARCHAR and then a complex If-Then-Else statement with length counts to decide on positional notation will be required.
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:
If you use a BASIC Transformer stage a suitable expression is
Note the comma inside the conversion specification of Oconv().
Code: Select all
Oconv(Iconv(InLink.TheMoney,"MD2"),"MD2,")
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.
I am using a Parallel tranformer stage, and the source field is decimal. I need to do a DecimaltoString conversion first on the source field, which I did as follows...
DecimalToString(Ilink1.Money,"fix_zero,suppress_zero").
I took the output from this parallel transformer to a next stage, a BASIC Transformer Stage, and applied the Oconv(Iconv(..))
Then I came to realize that I'm on an MPP system and Basic Transformer would not work here!
Is there something that I can do here and not use the BASIC Transformer stage?
I will try out Kenneth's suggestion as my last option if there is no other way to do it.
Thx
-N
DecimalToString(Ilink1.Money,"fix_zero,suppress_zero").
I took the output from this parallel transformer to a next stage, a BASIC Transformer Stage, and applied the Oconv(Iconv(..))
Then I came to realize that I'm on an MPP system and Basic Transformer would not work here!
Is there something that I can do here and not use the BASIC Transformer stage?
I will try out Kenneth's suggestion as my last option if there is no other way to do it.
Thx
-N
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can - expensively in terms of transferring data between nodes - limit your BASIC Transformer stage to run on the conductor node using a node pool.
Otherwise the parallel routine could operate "right to left" using Mod() function to identify each thousands component and build a string containing the thousands delimiter.
While you're at it, make the routine generic, so that it can also handle Indian convention (for example 1,00,00,000). Then maybe post your routine in the downloads area. Contact the Editor about this area; it's currently on a different server.
Otherwise the parallel routine could operate "right to left" using Mod() function to identify each thousands component and build a string containing the thousands delimiter.
While you're at it, make the routine generic, so that it can also handle Indian convention (for example 1,00,00,000). Then maybe post your routine in the downloads area. Contact the Editor about this area; it's currently on a different server.
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.
This has come up on other posts. We no longer have Oconv, Iconv, and FMT in Parallel. There is debate on if there are other functions that peform the same functionality in Parallel or not. Can someone provide a definitive answer, can the funtionality of these functions be done in Parallel with other functions? Is so, please provide some examples. If not I believe we have a number of customers that would demand this from IBM. If IBM will not create these, I will create them myself.
Grant Dever
Programmer Analyst Sr
Programmer Analyst Sr
Create a routine and use the following code -
Stay away from the BASIC transform if possible. Anything you can do in there (routines) you can do with routines or stages/operators in the parallel canvas.
Reference: http://www.dreamincode.net/code/snippet5076.htm
Code: Select all
Handles numbers of any size.
Snippet
#include <iostream>
#include <string>
using namespace std;
// Non-recursive method of adding commas to numbers as C++ strings ...
// s is string passed in, ns is new string returned by ref. with commas inserted
void addCommas( const string& s, string& ns )
{
int j, k, topI = s.length()-1;
do
{
for( j = topI, k = 0; j >= 0 && k < 3; --j, ++k )
{
ns = s[j] + ns; // new char is added to front of ns
if( j > 0 && k == 2) ns = "," + ns; // j > 0 means still more digits
}
topI -= 3;
}while( topI >= 0 );
}
Reference: http://www.dreamincode.net/code/snippet5076.htm
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com