• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Applying number format to a single table

 
Ranch Hand
Posts: 33
1
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
I'm trying to change number format of a column of a single table. If I use the following code-

COLUMN basic FORMAT 999,999;

then number format of all the tables, that contains a column named basic, will be changed. I want to restrict this form to a single table.

Can any one help with this problem?

Thanks in advance

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can overcome this limitation by aliasing the column in your select clause and specifying the format for the alias, this will mean the other basic columns won't be affected:

(I assume you know the COLUMN command only works in SqlPlus...)
 
Debajyoti Kundu
Ranch Hand
Posts: 33
1
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Martin for the quick response.

I was not aware COLUMN keyword does not work beyond SQL Plus.

Is there any other way to apply number format to a specific column of a specific table without using alias?
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You could use a view:However, this has a great disadvantage: basic is a textual column, not numeric one, and you can no longer perform some operations (such as calculating sum) on it. The utility of this approach is therefore pretty limited.

Formatting is usually the responsibility of the client application. Database holds just the data, client applications (and SqlPlus is a client application in this context) format the data to display them to the user. Furthermore, the same column might be formatted differently in different contexts (eg. sometimes the full figure, sometimes just the thousands).

Client applications can look at database metadata to adapt their formatting to the data, if the information isn't used. If your column is declared as NUMBER(6,0), the client application can get this information and decide to display it using '999,999' format (there will be at most 6 digits in the number). If the column is declared NUMBER(5,3), a '90.000' format might be more appropriate. However, if all your columns are declared just as NUMBER, you're out of luck - it can contain anything from 0.000000000001 to 1000000000000000 (just making it up, the real range is even larger, of course). SqlPlus itself inspects the metadata to determine the width of textual columns, for example.
 
I yam what I yam and that's all that I yam - the great philosopher Popeye. Tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic