Home » SQL & PL/SQL » Client Tools » format column problem
format column problem [message #521692] Thu, 01 September 2011 02:50 Go to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
SQL> col sid for a80
SQL>
SQL> select round(sofar*100/totalwork,2), sid from v$session_Longops where time_remaining > 0;

ROUND(SOFAR*100/TOTALWORK,2) SID
---------------------------- ----------
.05 ##########
80.09 ##########


hi,

i am learning how to format my query output. But i kept on getting this ##### ... I tried running without the [col sid for a80' and i manage to get the value. I seen the rest doing as the same as me but i dun seem to get it. please further advice.
Re: format column problem [message #521693 is a reply to message #521692] Thu, 01 September 2011 02:57 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
That's odd, what versions are you running?

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 1 08:56:12 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

scott@orcl> select round(sofar*100/totalwork,2), sid from v$session_Longops where time_remaining > 0;

ROUND(SOFAR*100/TOTALWORK,2)        SID
---------------------------- ----------
                        14.8       1503
                       38.23        210
                       11.47       4126
                       61.87       2444
                        2.41       5368
                        91.8        686
                       63.75       4678
                          37        365
                       44.48       2676
                       37.86       3907
                         6.1       4910
                       61.51        862
                       66.93       6174
                       58.24       2616
                       76.38       2008
                       62.97       1840
                       76.94       2906
                       30.13        863
                       41.82        300
                       66.66       3522
                       14.71       4181
                        61.8       2563
                       65.95       1585
                        5.06       3088
                       58.83       3584

25 rows selected.

Elapsed: 00:00:00.26
Re: format column problem [message #521694 is a reply to message #521692] Thu, 01 September 2011 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SID is a NUMBER not a STRING, so format should be in the form 9999999... not A..

Regards
Michel

[Edit: typo]

[Updated on: Thu, 01 September 2011 04:43]

Report message to a moderator

Re: format column problem [message #521695 is a reply to message #521694] Thu, 01 September 2011 03:14 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
tks for your immediate reply. i go figure out the number formatting.

Erm, RC, did u format it before you actually run the select statement? Cos when i just run the select code, i get what u get so i think is not the version problem.

I got tried again then get back to you guys. Thanks in advance!
Re: format column problem [message #521700 is a reply to message #521695] Thu, 01 September 2011 04:23 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
yea i got trial and error what it means to use those 9999 or 99.9 etc. At least now manage to see the value
Re: format column problem [message #521701 is a reply to message #521695] Thu, 01 September 2011 04:25 Go to previous message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
Nope - but if the output is perfectly readable, why 'format' it further?

Besides, as Michel said, your format is wrong for the column datatype, that's the problem.

[Updated on: Thu, 01 September 2011 04:26]

Report message to a moderator

Previous Topic: How to input line to sql script
Next Topic: SQLPLUS is not coming out with the exit code if not connected
Goto Forum:
  


Current Time: Thu Apr 17 08:00:05 CDT 2014

Total time taken to generate the page: 0.13220 seconds