All Forums Database
momitty 3 posts Joined 10/07
26 Oct 2007
Formatting for percentage and currency

I need a couple columns cast into strings (easy) but I also need one formatted as money and one formatted as a percent. I'm not aware of any built in functions to do this. Is anybody else? Or does anybody know a quick way to do this?The reason is I am trying to bring the results into a reporting application and because of some specific requirements by the end user certain values need to have text in the cells, but if the application thinks I am dealing with numbers it won't allow it, and if I bring it in as a string I lose the ability to format.

joedsilva 505 posts Joined 07/05
26 Oct 2007

you are almost there, apply the format clause fist (lookup the data types and literals manual for currency formats and pick your choice) and then cast the whole thing to a string.

momitty 3 posts Joined 10/07
29 Oct 2007

Do you have an explanation of the format clause? I thought it was just for timestamps?

joedsilva 505 posts Joined 07/05
29 Oct 2007

Format clauses are applicable to other data types as well, it's sort of a "hint" to the application querying the data as to how the data should be (desirably) displayed. But applications are free to ignore this display hint, and is the reason why no matter what format clause you apply, the JDBC and ODBC applications display the data in the same (their own) format.Teradata CLI applications like bteq, fast export etc, are built to recognise the format clause and then display the data accordingly. this is the reason, why you can always get them to display the data in whatever valid format you chose.when you apply formating on a data type and then convert it to a character data type, the formatting is done bye the database itself (who is smart for the job) and then it sends only a character string back (after converting to a char type), so from the application's perspective, it's NOT a number or date but a string data, which it displays as such ! This is the popular trick used to circumvent the limitation of ODBC and JDBC apps with regards to formating.

momitty 3 posts Joined 10/07
30 Oct 2007

I did some digging in a PDF that was on a CD and found the format function. It's actually really powerful, just takes a little trial and error for a dummy like me.Here's an example of what I was doing:select CAST( (CSEXTPRC(format '$$--------9') ) AS VARCHAR(10) ), CAST( (PODAY(format '----9%') ) AS VARCHAR(10) )from RPT_VIEWS.cshdet_unaud The biggest thing I found is putting the dashes in so it doesn't put leading zeros on.Thanks for all your help!

You must sign in to leave a comment.