Formatting for percentage and currency

Database
Highlighted

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.



4 REPLIES
Enthusiast

Re: Formatting for percentage and currency

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.

Re: Formatting for percentage and currency

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

Re: Formatting for percentage and currency

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.

Re: Formatting for percentage and currency

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!