File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Applying number format to a single table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Applying number format to a single table" Watch "Applying number format to a single table" New topic
Author

Applying number format to a single table

Debajyoti Kundu
Greenhorn

Joined: Sep 14, 2011
Posts: 23
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

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Greenhorn

Joined: Sep 14, 2011
Posts: 23
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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 agree. Here's the link: http://aspose.com/file-tools
 
subject: Applying number format to a single table