OraFAQ Forum: SQL & PL/SQL » 2nd Decimal Place not appearing

Home » SQL & PL/SQL » SQL & PL/SQL » 2nd Decimal Place not appearing

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

2nd Decimal Place not appearing [message #561115]

Thu, 19 July 2012 00:58

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

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561117 is a reply to message #561115]

Thu, 19 July 2012 01:01

Littlefoot
Messages: 17256
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')

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561120 is a reply to message #561117]

Thu, 19 July 2012 01:05

praveen240881
Messages: 36
Registered: February 2012

Member

No it didnt work. still printing the whole number only

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561121 is a reply to message #561120]

Thu, 19 July 2012 01:13

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

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561123 is a reply to message #561121]

Thu, 19 July 2012 01:19

Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How exactly do you write into the file?

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561131 is a reply to message #561123]

Thu, 19 July 2012 01:27

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'),

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561140 is a reply to message #561131]

Thu, 19 July 2012 01:33

Littlefoot
Messages: 17256
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.

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561141 is a reply to message #561131]

Thu, 19 July 2012 01:34

Michel Cadot
Messages: 54719
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

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561146 is a reply to message #561141]

Thu, 19 July 2012 01:40

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');

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561148 is a reply to message #561146]

Thu, 19 July 2012 01:44

Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is invalid code which would NEVER compile.

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561150 is a reply to message #561148]

Thu, 19 July 2012 01:55

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.

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561152 is a reply to message #561150]

Thu, 19 July 2012 02:07

Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I see. Unfortunately, what you posted doesn't make any difference to me.

Report message to a moderator

Re: 2nd Decimal Place not appearing [message #561160 is a reply to message #561150]

Thu, 19 July 2012 04:02

c_stenersen
Messages: 253
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.)

Report message to a moderator

Previous Topic:	SQL query
Next Topic:	how can i lock particular row in a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 20 01:23:50 CDT 2013

Total time taken to generate the page: 0.11473 seconds