Home » SQL & PL/SQL » SQL & PL/SQL » 2nd Decimal Place not appearing
2nd Decimal Place not appearing [message #561115] Thu, 19 July 2012 00:58 Go to next message
praveen240881
Messages: 36
Registered: February 2012
Member
Dear Experts

I need your guidance please.
I am using Oracle Forms 6i. In my program unit i write into a .CSV and .TXT File. Here if the amount is 100.00 then it prints as 100 only. That is if the 2nd decimal digit is 0 it doesnt print it.

For example
10.00 -->10
10.01--> 10.01
10.10-->10.1
10.12-->10.12

I used to_char(nvl(&amount,0),'9999999.99')
so if query select to_char(nvl(10.10,0),'9999999.99') from dual, then i get the output as 10.10.

Do you think this is a problem with Oracle forms or SQL or the formatting of .TXT & .CSV.

Praveen
Re: 2nd Decimal Place not appearing [message #561117 is a reply to message #561115] Thu, 19 July 2012 01:01 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you substitute format mask from
to_char(nvl(&amount,0),'9999999.99')
to
to_char(nvl(&amount,0),'9999990.00')
Re: 2nd Decimal Place not appearing [message #561120 is a reply to message #561117] Thu, 19 July 2012 01:05 Go to previous messageGo to next message
praveen240881
Messages: 36
Registered: February 2012
Member
No it didnt work. still printing the whole number only
Re: 2nd Decimal Place not appearing [message #561121 is a reply to message #561120] Thu, 19 July 2012 01:13 Go to previous messageGo to next message
praveen240881
Messages: 36
Registered: February 2012
Member
Look like the problem is with printing the value onto the text or csv file. bceause i debugged the code and found out the the variable is capturing 100.00, just that while writing onto the file it omits the 2nd decimal 0
Re: 2nd Decimal Place not appearing [message #561123 is a reply to message #561121] Thu, 19 July 2012 01:19 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How exactly do you write into the file?
Re: 2nd Decimal Place not appearing [message #561131 is a reply to message #561123] Thu, 19 July 2012 01:27 Go to previous messageGo to next message
praveen240881
Messages: 36
Registered: February 2012
Member
I have a Procedure A where my IN Parameter (P_Amount). P_Amount is NUMBER

Then i call this Procedure A in another Program Unit B and then write the value into the File. Like

PROCEDURE FILL_LINE_BUFFER(P_Amount NUMBER);

line_buffer := fill_line_buffer(to_char(nvl(v_line_amount, 0),'9999990.00'),
Re: 2nd Decimal Place not appearing [message #561140 is a reply to message #561131] Thu, 19 July 2012 01:33 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not familiar with "FILL_LINE_BUFFER" procedure (did you write it? What does it do? What Oracle built-ins does it use?); hopefully, someone else will be able to help.
Re: 2nd Decimal Place not appearing [message #561141 is a reply to message #561131] Thu, 19 July 2012 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
No it didnt work. still printing the whole number only


Where do you "print" (or see) the string.

Post the code of the procedure.

Regards
Michel
Re: 2nd Decimal Place not appearing [message #561146 is a reply to message #561141] Thu, 19 July 2012 01:40 Go to previous messageGo to next message
praveen240881
Messages: 36
Registered: February 2012
Member
FUNCTION fill_line_buffer(p_Amount     NUMBER,) 
RETURN VARCHAR2 IS
  line_buffer varchar2(2000);
BEGIN
  line_buffer := line_buffer || p_Amount || ',';
   RETURN(line_buffer);
END;


PROCEDURE PRINT_AMOUNT
line_buffer :=fill_line_buffer (to_char(nvl(v_line_amount, 0),'9999999.99');
Re: 2nd Decimal Place not appearing [message #561148 is a reply to message #561146] Thu, 19 July 2012 01:44 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is invalid code which would NEVER compile.
Re: 2nd Decimal Place not appearing [message #561150 is a reply to message #561148] Thu, 19 July 2012 01:55 Go to previous messageGo to next message
praveen240881
Messages: 36
Registered: February 2012
Member
i just pasted the portion that i thought will be useful. because the whole code is over 1000+ lines.
Re: 2nd Decimal Place not appearing [message #561152 is a reply to message #561150] Thu, 19 July 2012 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see. Unfortunately, what you posted doesn't make any difference to me.
Re: 2nd Decimal Place not appearing [message #561160 is a reply to message #561150] Thu, 19 July 2012 04:02 Go to previous message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
What you're sending in to your function is a string, but the function expects a number, so it does an implicit conversion. Then the functions appends that number to your varchar2 variable which means that it's doing another implicit conversion to get the number as a string again. Your to_string should be in the function, not in the function call.

(That you didn't check your little example for syntax errors is a different matter. For the future, please do.)
Previous Topic: SQL query
Next Topic: how can i lock particular row in a table
Goto Forum:
  


Current Time: Mon Jul 28 19:16:01 CDT 2014

Total time taken to generate the page: 0.05342 seconds