Home » SQL & PL/SQL » SQL & PL/SQL » format data with 4 decimal
format data with 4 decimal [message #284200] Thu, 29 November 2007 02:52 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all,

i wants to format a feild which is having currancy data in it
that to with 4 decimal

data stored in table is

MKT_RATE
719.850000
502.320000
20.730000
894.408000
503.573800
719.676500


but i want it like this

MKT_RATE
719.8500
502.3200
20.7300
894.4080
503.5738
719.6765



i tried it with round () , cast(col_name as decimal (9,4))

but it does not satisfy the condition
please suggest some other function

sonal

Re: format data with 4 decimal [message #284202 is a reply to message #284200] Thu, 29 November 2007 02:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That would be the "to_char" function.

Example :

SELECT to_char(14.32532523523,'99999990d0000') FROM dual;

[Updated on: Thu, 29 November 2007 02:57]

Report message to a moderator

Re: format data with 4 decimal [message #284204 is a reply to message #284202] Thu, 29 November 2007 03:00 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
I cant change datatype to char because it wont use calculation with it
it is currancy data & having int datatype

someother way
Re: format data with 4 decimal [message #284208 is a reply to message #284200] Thu, 29 November 2007 03:05 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

You no need to change the datatype of the field 'MKT_RATE'.

When you selecting the field MKT_RATE you should use function TO_CHAR.

Do you want to update it in Database or just to display it?

Regards,
Kiran.
Re: format data with 4 decimal [message #284210 is a reply to message #284204] Thu, 29 November 2007 03:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:


I cant change datatype to char because it wont use calculation with it



So you will have to decide if you want to FORMAT ( use to_char ) it for output, of if you want to CALCULATE ( use round ) further with it.
Re: format data with 4 decimal [message #284213 is a reply to message #284200] Thu, 29 November 2007 03:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Then what is the issue with round and Trunc ? Show How did you try ?

Thumbs Up
Rajuvan.
Re: format data with 4 decimal [message #284219 is a reply to message #284210] Thu, 29 November 2007 03:24 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
i want it disply it but i will work on this output after exporting it to xsl file

Re: format data with 4 decimal [message #284220 is a reply to message #284200] Thu, 29 November 2007 03:27 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Then,you can use


SELECT TO_NUMBER (TO_CHAR (mkt_rate, '99999990d0000'))
  FROM tab


Kiran.
Re: format data with 4 decimal [message #284230 is a reply to message #284220] Thu, 29 November 2007 03:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
@kir_ait

Ehh?

That would give him the same thing as

SELECT round(mkt_rate,4) FROM tab


But when it's an XLS File, and he wants it as NUMBER in the XLS, but formated to 4 decimals, then there is no direct way to do it in SQL.

[Updated on: Thu, 29 November 2007 03:37]

Report message to a moderator

Re: format data with 4 decimal [message #284592 is a reply to message #284200] Fri, 30 November 2007 04:24 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Try this,


SELECT SUBSTR(MKT_VALUE,1,INSTR(VALUE,'.')+4) FROM <<table_name>>;



Thanks,
Sam
Re: format data with 4 decimal [message #284595 is a reply to message #284592] Fri, 30 November 2007 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

JokingSam

Regards
Michel
Re: format data with 4 decimal [message #284596 is a reply to message #284200] Fri, 30 November 2007 04:29 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

INSTR is not supposed to use over Numbers

Thumbs Up
Rajuvan
Previous Topic: work out age using two columns
Next Topic: retrieval of employee name
Goto Forum:
  


Current Time: Sun Dec 04 06:51:32 CST 2016

Total time taken to generate the page: 0.06382 seconds