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 print decimal as 0.****

Discussion in 'SQL PL/SQL' started by RG Hegde, Jul 28, 2015.

  1. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello,

    I need to print decimal in o.*****.

    Now it is printing as .******

    Can any one please provide me the format.
     
  2. jagadekara

    jagadekara Forum Guru

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

    Not clear. Where you want it?

    In Sql or in any report output?

    provide sample data and required data
     
  3. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    I have to print that using fnd_file.put_line. The data is in table . the sample data is

    0.24766
    0.27092
    0.55931
    0.51130
    0.27423
    It is printing as .24766 not printing 0

    You can check that using DBMS_OUTPUT.PUT_LINE also
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    You are using in fnd_file.put_line.
    So what is the output type? Is it pdf or excel?
    Also are you using xml publisher?
     
  5. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    No it is simple text output...
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can address that in the following way:

    Code (SQL):
    SQL> CREATE TABLE zero(
      2          mynum   NUMBER(12,6));

    TABLE created.

    SQL>
    SQL> INSERT ALL
      2  INTO zero
      3  VALUES (0.24766)
      4  INTO zero
      5  VALUES (0.27092)
      6  INTO zero
      7  VALUES (0.55931)
      8  INTO zero
      9  VALUES (0.51130)
    10  INTO zero
    11  VALUES (0.27423)
    12  SELECT * FROM dual;

    5 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> DECLARE
      2          cursor getdat IS
      3          SELECT mynum FROM zero;
      4  BEGIN
      5          FOR irec IN getdat loop
      6                  dbms_output.put_line('0'||irec.mynum);
      7          END loop;
      8  END;
      9  /
    0.24766
    0.27092
    0.55931
    0.5113
    0.27423

    PL/SQL PROCEDURE successfully completed.

    SQL>
     
    RG Hegde likes this.
  7. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Thanks David,

    Your logic will work. It will add extra 0 . eg : 230.2345 the output will be 0230.2345. I have modified the logic as

    Code (SQL):
     dbms_output.put_line(DECODE(SUBSTR(irec.mynum,1,INSTR(irec.mynum,'.',1)-1),NULL,'0',NULL)||irec.mynum);
    Please share if any other simplest logic in your mind
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My response was a quick 'fix' to a problem since I have other issues to attend to at work at this time. It was not meant to be a complete solution so modifications are to be expected.
     
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Wouldn't a number format model give you the results you want?

    Code (Text):
    BEGIN
      DBMS_OUTPUT.PUT_LINE(0.252345);
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(0.252345, '9990.999999'));
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(230.2345, '9990.999999'));
    END;

    .252345
        0.252345
      230.234500
     
    RG Hegde likes this.
  10. shareefirfan

    shareefirfan Forum Advisor

    Messages:
    20
    Likes Received:
    8
    Trophy Points:
    485
    Location:
    Hindupur, AP, India
    Use '0' before decimal point.

    dbms_output.put_line(TO_CHAR(0.252345, '0.000000'));

    Thanks,
    Irfan.
     
  11. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    @Matthew
    Oops I tried with to_char(field,'09999.9999') .

    Yes it will work but I am not sure how many digits number will be received.

    It might be 0.12345
    12345
    12345.123456678

    The max size in the file is 20 digits and I dont want to print trailing zeros like 123.45600000
     
  12. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    So add an RTRIM:

    Code (Text):

    BEGIN
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(0.252345, '9990.99999999999999999999'));
      DBMS_OUTPUT.PUT_LINE(RTRIM(TO_CHAR(0.252345, '9990.99999999999999999999'), 0));
    END;

        0.25234500000000000000
        0.252345
     
    RG Hegde likes this.
  13. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Perfect . Thanks Matthew