Formatting sqlplus results [message #19531] |
Mon, 25 March 2002 10:39 |
neel
Messages: 12 Registered: November 2001
|
Junior Member |
|
|
I have a sql which is
for eg :
column price1 format 00000v0000
column price2 format 00000v0000
select col a, col b, col c ,
to_char(price1,'00000v0000'), to_char(price2,'00000v0000') from temp
where --------------
Here cola -- 22bytes
colb -- 20 bytes
colc -- 45 bytes (cola, colb and colc is char data)
price1 -- number(13,4)
price2 -- number(13,4)
We want to show the character columns as it is and
number columns as (10,4) with implied decimal i.e a total of 10 bytes for each price. we want the numeric columns to show leading zero instead of spaces.
My results shows the leading zero's but after colc there is a space and also before price 2 there is a space. Including the spaces the price1 and prcie2 is 1o bytes each (i.e 9 digits and 1 space). My problem is i don't need to show the space. Can anyone explain me how to avoid it and give me some ideas to improve the sql. I also tried using 'FM' in the to_char functions but didn't help.
Thanks
|
|
|
|
Re: Formatting sqlplus results [message #19550 is a reply to message #19544] |
Wed, 27 March 2002 05:25 |
neel
Messages: 12 Registered: November 2001
|
Junior Member |
|
|
No it is still not working. It still shows a space for the number columns i.e 9 digits and a space.
I want the result to be something like
for eg : the number is 123.45, the format should be
001234500 , but my sql shows the results as
space001234500.
I don't know where it is going wrong. I have tried using 09999v0000 format too.
Thanks
|
|
|
|
Re: Formatting sqlplus results [message #21304 is a reply to message #19531] |
Thu, 25 July 2002 07:25 |
Jey
Messages: 4 Registered: February 2001
|
Junior Member |
|
|
I have the same problem. Try this one.
to_char(price1,'FM00000v0000') || to_char(price2,'FM00000v0000')
Here I have '||' instead of comma and have added FM.
|
|
|