5 Replies Latest reply on May 8, 2007 1:45 AM by ushitaki

    Conversion of NUMBER to DECIMAL

    569815
      I am querying a field that is a NUMBER(3,1) data type and I need to have it returned as a DECIMAL with two decimal points. For example, 5 would be returned as 5.00.

      Thanks,
      Sushi Lover
        • 1. Re: Conversion of NUMBER to DECIMAL
          marias
          try like this...

          SQL> select sal,to_char(sal,'99999999.99') sal from emp;

          SAL SAL
          ---------- ------------
          1250 1250.00
          3200 3200.00
          800 800.00
          882 882.00
          1764 1764.00
          1377 1377.00
          3279 3279.00
          1377 1377.00
          • 2. Re: Conversion of NUMBER to DECIMAL
            569815
            That converts it to a CHAR. Is it possible to convert it to a DECIMAL?
            • 3. Re: Conversion of NUMBER to DECIMAL
              William Robertson
              CAST(col AS NUMBER(4,2))

              Or are you talking about display formatting? If so it will depend on the client application. For example in SQL*Plus,
              SQL> desc t
              Name                    Null?    Type
              ----------------------- -------- ----------------
              N                                NUMBER(3,1)

              SQL> select * from t;

                       N
              ----------
                    12.3

              1 row selected.

              SQL> col n format 990.00
              SQL> r
                1* select * from t

                    N
              -------
                12.30

              1 row selected.
              or more portable, avoiding hardcoding the decimal point character:
              SQL> col n format 990D00
              SQL>
              SQL> r
                1* select * from t

                    N
              -------
                12.30

              1 row selected.
              • 4. Re: Conversion of NUMBER to DECIMAL
                MichaelS
                DECIMAL and NUMBER are exactly the same thing in oracle. You simply need to format the output
                michaels>  col dec format a30
                michaels>  col num format a30
                michaels>  col sal format 9999.00
                
                michaels>  SELECT DUMP (CAST (sal AS DECIMAL (5, 1))) DEC,
                       DUMP (CAST (sal AS NUMBER (5, 1))) num,
                       sal
                  FROM emp
                /
                DEC                            NUM                                 SAL
                ------------------------------ ------------------------------ --------
                Typ=2 Len=2: 194,9             Typ=2 Len=2: 194,9               800.00
                Typ=2 Len=2: 194,17            Typ=2 Len=2: 194,17             1600.00
                Typ=2 Len=3: 194,13,51         Typ=2 Len=3: 194,13,51          1250.00
                Typ=2 Len=3: 194,30,76         Typ=2 Len=3: 194,30,76          2975.00
                Typ=2 Len=3: 194,13,51         Typ=2 Len=3: 194,13,51          1250.00
                Typ=2 Len=3: 194,29,51         Typ=2 Len=3: 194,29,51          2850.00
                Typ=2 Len=3: 194,25,51         Typ=2 Len=3: 194,25,51          2450.00
                Typ=2 Len=2: 194,31            Typ=2 Len=2: 194,31             3000.00
                Typ=2 Len=2: 194,51            Typ=2 Len=2: 194,51             5000.00
                Typ=2 Len=2: 194,16            Typ=2 Len=2: 194,16             1500.00
                Typ=2 Len=2: 194,12            Typ=2 Len=2: 194,12             1100.00
                Typ=2 Len=3: 194,10,51         Typ=2 Len=3: 194,10,51           950.00
                Typ=2 Len=2: 194,31            Typ=2 Len=2: 194,31             3000.00
                Typ=2 Len=2: 194,14            Typ=2 Len=2: 194,14             1300.00
                
                
                14 rows selected.
                • 5. Re: Conversion of NUMBER to DECIMAL
                  ushitaki
                  In Oracle DECIMAL is alias of NUMBER.

                  If you want to display with two decimal points format
                  you ought to convert it to character for displaying
                  or set your tools to format columns.
                  SQL> create table test(num number(3,1));

                  Table created.

                  SQL> insert into test values (12.1);

                  1 row created.

                  SQL> column num42 format 99.00
                  SQL> column dec42 format 99.00
                  SQL> select num,cast(num as decimal(4,2)) as dec
                    2        ,num as num42, cast(num as decimal(4,2)) as dec42
                    3  from test;

                         NUM        DEC  NUM42  DEC42
                  ---------- ---------- ------ ------
                        12.1       12.1  12.10  12.10