0

This is my insert into statement:

INSERT INTO PRODUCT 
VALUES 
('PROD010', 'Kiwi milk tea', 'A milk tea that made from kiwi powder and other ingredients.', 4.50, 5.00);

But,what appear on the screen are PROD010, Kiwi milk tea, A milk tea that made from kiwi powder and other ingredients, 4.5 and 5

All zeros of 4.50 and 5.00 are disappear.

So, what should I do now so that all zeros can be displayed??

Codes of PRODUCT table:

    CREATE TABLE PRODUCT(
    PROD_ID         VARCHAR(10) NOT NULL,
    PROD_NAME       VARCHAR(30),
    PROD_DESC       VARCHAR(70),
    R_UNIT_PRICE    NUMBER(3,2),
    L_UNIT_PRICE    NUMBER(3,2),
    primary key (PROD_ID)
    );
user3310635
  • 53
  • 1
  • 8
  • Wahts the column type on that value?You need to adjust the precision – Mihai Mar 07 '14 at 13:49
  • The codes of PRODUCT table is there. – user3310635 Mar 07 '14 at 14:01
  • Oracle is hiding insignificant zeroes. If you want to allways show 2 zeroes after comma in your select statements, use something like this `SELECT TO_CHAR(4.5,'9.99') FROM DUAL` – Mikhail Mar 07 '14 at 14:08
  • How about if I want to show all zeroes?Because there are other data in other tables that face this problem also. – user3310635 Mar 07 '14 at 14:26
  • So what? `4.5` is the same value as `4.50`. If you want trailing zeros use `to_char()` to format your values. –  Mar 08 '14 at 10:46
  • You don't need help inserting... you need help retrieving. – Hein Mar 08 '14 at 16:44

2 Answers2

2

There's nothing you can change in the table to make the client display trailing, insignificant, zeros. (At least while keeping it as a number; don't even consider storing it as a string). The numeric value is stored in an efficient internal format. When the client retrieves the value it is converted into the client's natice numeric format, and the client decides how it should be displayed.

To show zeros in a client like SQL*Plus, including 0.00, you can retrieve it as a string:

select prod_id, prod_name, prod_desc,
  to_char(r_unit_price, '0.00') as r_unit_price,
  to_char(l_unit_price, '0.00') as l_unit_price
from product;

SQL Fiddle demo.

You will need to do that in each query where it matters, unless your client application does its own formatting. When you are using the value in a calculation leave it as a number while it's being used, and apply the format model to the final disolayed result - otherwise you'll have pointless implicit conversions to and from strings, and the potential for errors.

Your data type is very restrictive - you can't have a value of 10 or above. That might be what you want, but review the precision and scale information Mihai linked to.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0
select num, length(num) len, instr(num,separator) dot
      ,case when instr(num,separator) > 0 then length(num) - instr(num,separator) else 0 end as dot2
      ,case when instr(num,separator) > 0 then rpad(num,instr(num,separator)+2,0) else num||separator||'00' end as num2
  from ( select ',' as separator from dual ) sep,
  ( 
  select 1.21   as num from dual union all
  select 1.2    as num from dual union all
  select 9      as num from dual union all
  select 11.456 as num from dual union all
  select 341.2  as num from dual 
  );

NUM     LEN DOT DOT2    NUM2
1,21    4   2   2   1,21
1,2     3   2   1   1,20
9       1   0   0   9,00
11,456  6   3   3   11,45
341,2   5   4   1   341,20

NUM2 is the answer.
Change separator do comma or dot, depending on Your environment.

Jakub P
  • 542
  • 4
  • 21