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.
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.