Home » SQL & PL/SQL » SQL & PL/SQL » To_number function
To_number function [message #388564] Wed, 25 February 2009 07:57 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi i want to display the number with decimals & right aligned for example 1.000
i tried by using to_number but it is displaying as "1"
Can anyone help me ?
Re: To_number function [message #388566 is a reply to message #388564] Wed, 25 February 2009 08:00 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
What tool are you using to display?
Look at the SQL*Plus COL command or TO_CHAR

[Updated on: Wed, 25 February 2009 08:00]

Report message to a moderator

Re: To_number function [message #388568 is a reply to message #388564] Wed, 25 February 2009 08:02 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Or NLS-settings?
What's the decimal sign? A dot '.' or a comma ','

Makes a big difference when the value is 1.000
Re: To_number function [message #388569 is a reply to message #388566] Wed, 25 February 2009 08:03 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
i am using pl/sql developer
Re: To_number function [message #388570 is a reply to message #388569] Wed, 25 February 2009 08:05 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
ramya29p wrote on Wed, 25 February 2009 15:03
i am using pl/sql developer


And what's the exact SQL-statement you tried?
Re: To_number function [message #388571 is a reply to message #388570] Wed, 25 February 2009 08:09 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
i tried the below query
select to_number(1.000,'9999.9999') from dual

but it is displaying the output as 1

[Updated on: Wed, 25 February 2009 08:09]

Report message to a moderator

Re: To_number function [message #388572 is a reply to message #388571] Wed, 25 February 2009 08:11 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I would suggest you (re-)read the documentation on TO_NUMBER and TO_CHAR Wink

TO_NUMBER is used to convert a string into a numeric value
TO_CHAR is used to convert a numeric value into a string.

I suppose you need the second.
Re: To_number function [message #388578 is a reply to message #388572] Wed, 25 February 2009 08:36 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
ya if i use the below query
select to_char(1.000,'999999.9999') from dual

i am getting the correct value but it is not right aligned
Re: To_number function [message #388580 is a reply to message #388578] Wed, 25 February 2009 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select to_char(1.000,'999999.9999') from dual;
TO_CHAR(1.00
------------
      1.0000

1 row selected.

What do you mean it is not right aligned?

Regards
Michel

[Updated on: Wed, 25 February 2009 08:46]

Report message to a moderator

Re: To_number function [message #388581 is a reply to message #388564] Wed, 25 February 2009 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i am getting the correct value but it is not right aligned
What exactly are YOU doing?
How exactly is Oracle responding?
What exactly do you expecting/desire.

use CUT & PASTE to show us!
Re: To_number function [message #388584 is a reply to message #388580] Wed, 25 February 2009 08:53 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
He uses SQL*Developer.

I have never used it myself, but it could be that the result is displayed in some sort of "GUI table", where it is decided on whether the result is a char or a number to left/right align the data.

It's the old "Those who live by the GUI die by the GUI", I think Grin
Re: To_number function [message #388599 is a reply to message #388578] Wed, 25 February 2009 10:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is right aligned, in that there are spaces to the left of the output string, but in SQL Developer, the column output isn't right aligned with respect to the column heading.
Re: To_number function [message #388771 is a reply to message #388599] Thu, 26 February 2009 03:18 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
I am loading the value from the table to Excel.i have attached the sample xls file.The amount field is displaying without decimals.i have used the below query
   select 
        a.value_dt value_dt,
        a.trn_ref_no ref_no,               
       to_char( a.lcy_amount,'99999999999999.99') gl_amt       
   from actb_daily_log   a,
        sttb_account     b
  where a.ac_no = b.ac_gl_no and b.gl_aclass_type = '1' and
        b.ac_or_gl = 'A'  and      
         b.branch_code = 'PR1';

[Updated on: Thu, 26 February 2009 03:24]

Report message to a moderator

Re: To_number function [message #388775 is a reply to message #388771] Thu, 26 February 2009 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No file attached.

That sounds like an excel issue - if you don't give the cell an explicit numeric format mask, it will (quite correctly) truncate trailing decimal zeroes
Re: To_number function [message #388776 is a reply to message #388775] Thu, 26 February 2009 03:28 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
I have attachd the ScreenShot of the Excel Sheet
  • Attachment: untitled1.jpg
    (Size: 191.87KB, Downloaded 533 times)
Re: To_number function [message #388778 is a reply to message #388776] Thu, 26 February 2009 03:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Now right-click on one of those cells, pick Format from the list, set it to Number, and see what it displays.

Nb, this is ORAFaq, not ExcelFaq
Re: To_number function [message #388783 is a reply to message #388778] Thu, 26 February 2009 03:51 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Yes i know this.but i want to upload the value with decimals(without changing the cell format). thats why i posted here

[Updated on: Thu, 26 February 2009 03:57]

Report message to a moderator

Re: To_number function [message #388792 is a reply to message #388783] Thu, 26 February 2009 04:18 Go to previous message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is Excel that changes the type not Oracle.
So this has nothing to do with Oracle.
Post your question in an Excel forum.

Regards
Michel
Previous Topic: Help with SMTP Mail Code
Next Topic: Using Spool & double quote to CSV file
Goto Forum:
  


Current Time: Wed Nov 13 06:52:19 CST 2024