DSXchange

Posted: **Thu Feb 14, 2008 3:36 pm**

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.

Posted: **Thu Feb 14, 2008 9:47 pm**

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.

Posted: **Thu Feb 14, 2008 10:39 pm**

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().

Posted: **Fri Feb 15, 2008 12:14 pm**

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

Posted: **Fri Feb 15, 2008 5:26 pm**

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.

Posted: **Fri Nov 05, 2010 1:23 pm**

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.

Posted: **Fri Nov 05, 2010 2:07 pm**

devo wrote:This has come up on other posts.

Great userid, spud.

Posted: **Fri Nov 05, 2010 3:33 pm**

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

DSXchange

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

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