Hello. Anyone know if you can format numeric output in a compass sql query?
For example, if a value is stored as 123456, we would like to show it as 123,456.
We couldn't find anything in the compass sql reference. Thanks!
Andy made an interesting observation that there was an undocumented compass sql function. That got me to thinking maybe Infor is just repackaging something within AWS to make compass sql work. I searched for functions within the Amazon Athena documentation (https://docs.aws.amazon.com/athena/latest/ug/functions-env3.html). The Conversion link there lead me to this documentation (https://trino.io/docs/current/functions/conversion.html) - which contains information about the format_number and format functions. If I put their examples in a Compass query, I get the following results:So to answer your question, this should work:SELECT format('%,.0f', 123456)
I can't seem to find this documented, but there is a FORMAT_NUMBER function. I tried a few different additional arguments but couldn't seem to find any that worked. It might be worthwhile exploring though...
That's awesome Brandon! Great detective work!
Yes! Thanks Brandon, that is a huge help!
Hi all,
Unless the function is documented in the Data Fabric User Guide - Compass SQL Reference, it means the function is not officially supported and may get removed or changed.
Instead of relying on unsupported function, I recommend that in Concierge you create an Enhancement Requests, for the Infor OS product line and the Data Fabric product, with your use case and propose a possible solution direction. Other customers will be able to also endorse your request.
Infor will evaluate the enhancement for the development roadmap to include the necessary functionality and officially support it.
In case you create the ER, please also post the ER number here as it seems there are other users interested to endorse the function. Thank you!
Replying to this as well it is marked as the "Answer". Please see my other reply below. We advise against using undocumented functionality as it may stop working and break your integrations. Instead please create ER so that Infor can include the functionality you require as part of the product.
Hi RichP-CRH,
Also, it depends on the datatype you store your values in. In our case numbers are serialized and stored in the string format and the following query worked really nice (Thanks to one of the colleagues in helping to prepare it). Hope it gives some hint.
