1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to display Large amounts

Discussion in 'SQL PL/SQL' started by jagadekara, Jun 24, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    hi All,

    If i have huge amount, then how can I display this amount in excel sheet?

    For example
    select sum(invoice_amount) from ap_invoices_all
    1234567890123456

    If I run same query in xmlp report which is giving excel output then it is displaying 1234567890123450. I know that excel cell max limit is 15 digits. So how can I display 16 digits?

    Note that it's not any requirement. I just want to know this....
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    if to transform to string with separators of discharges and to unload value as string ?
    Code (SQL):

    SELECT to_char (1234567890123456,'FM9G999G999G999G999G999') FROM dual;
     
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    The transformation of the number into char, as posted above, with separator works fine in producing the XML output.

    However, the major issue lies with Excel: even with the thousands separators, Excel will automatically convert the "char" into Number and perform the same rounding.

    You need to change the rtf template also:

    - Your amount field / tag (for ex. "AMOUNT") should be of type "Regular text"
    - You should concatenate a "=" and surround the value with double quotes("): <?concat('=','"',AMOUNT,'"')?> [instead of just <?AMOUNT?>]

    Note: If you want decimals, just add "D90" in Sergey's format (I recall you had that issue in one of your previous post :)), i.e.,

    Code (SQL):
    SELECT to_char (1234567890123456.43,'FM9G999G999G999G999G999D90') FROM dual;
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks All for your valuable answers...

    I will let you know once I done.