How to add thousand-separator comma's to decimal field

[navistar]

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.

[kcbland]

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.

[ray.wurlod]

If you use a BASIC Transformer stage a suitable expression is

Code: Select all

Oconv(Iconv(InLink.TheMoney,"MD2"),"MD2,")

Note the comma inside the conversion specification of Oconv().

[navistar]

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

[ray.wurlod]

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.

[devo]

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.

[dougcl]

This has come up on other posts.

Great userid, spud.

[mhester]

Create a routine and use the following code -

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 );
}
 

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