5 Replies Latest reply: Dec 11, 2012 10:37 AM by 979256 RSS

    How to add 02 decimal to a number using LPAD and REPLACE .. Please help

    979256
      What I am trying to do is to have a variable with five digits with two decimal :

      I want to format 33 to 033.00 or 33.5 to 033.50

      My Code:

      DECLARE
      v_traffic_total NUMBER(5,2) :=0;
      v_traffic_total1 NUMBER(5,2) :=0;
      v_code_B_total VARCHAR2(11);
      v_code_B_total1 VARCHAR2(11);
      BEGIN

      v_traffic_total:= v_amount_traffic - ABS(v_amount_credit) - ABS(v_amount_freemins) - ABS(v_amount_consort);
      v_traffic_total:=33; -- amount total of traffic
      v_traffic_total1:=33.5; -- amount total of traffic1

      DBMS_OUTPUT.PUT_LINE(' AMOUNT traffic B : ' || v_traffic_total) ; it displays 33 but I want to have 33.00
      DBMS_OUTPUT.PUT_LINE(' AMOUNT traffic1 B : ' || v_traffic_total1) ; it displays 33.5 but I want to have 33.50

      ------ Then I use lpad to make 33.00 to look like 033.00 and 33.50 to like 033.50
      --and then use REPLCACE to remove the '.' after the decimal and finally display: B03300 or B03350

      v_code_B_total :=''B'||LPAD( v_traffic_total,5,0);
      v_code_B_total1 :=''B'||LPAD( v_traffic_total1,5,0);
      v_code_B_total:= REPLACE(v_code_B_total,'.', '') ;
      v_code_B_total1:= REPLACE(v_code_B_total1,'.', '') ;
      DBMS_OUTPUT.PUT_LINE(' AMOUNT traffic B : ' || v_code_B_total) ;
      DBMS_OUTPUT.PUT_LINE(' AMOUNT traffic B : ' || v_code_B_total) ;

      Unfortunately I have the following:
      B00033
      B00335
      BUT I need the results to be : B03300
      B03350


      Please help
      Hope