2

I need to show a dollar range in a query but when I concatenate the values I lose the comma in the output.

select
'$'||CAST(ROUND(MIN(db.INITIAL_BALANCE),-1) AS money)
||' to '||
'$'||CAST(ROUND(MAX(db.INITIAL_BALANCE),-1) AS money) AS Balance_Range

the output is $2060 to $73690

desired output $2,060 to $73,690

Any suggestions on this?

Intersystems Cache is my database

CoSpringsGuy
  • 1,615
  • 1
  • 13
  • 16

2 Answers2

1

Why do you think, that you even had commas ?
The documentation says nothing about formatting value.

MONEY and SMALLMONEY are currency numeric data types. The scale for currency data types is always 4.

SELECT CAST('123123' AS money)

this query will return 123123.0000, as expected

As I see, at least there are two ways how to do it.

  • Define your own datatype, same as %Currency as an example, where you can change scale number, and format. But you should change to this type all properties, in all classes, where you need it.
  • Or you can define new SQL Function, which will format all numbers as needed.

Something like below: 

Class Sample.Utils Extends %RegisteredObject
{

ClassMethod ToMoney(pValue As %Integer = 0) As %String [ SqlProc, SqlName= "TO_MONEY"]
{
    quit "$ " _ $fnumber(pValue, ",", 2)
} 

}

And Query SELECT Sample.TO_MONEY(1234567), will return $ 1,234,567.00

DAiMor
  • 3,185
  • 16
  • 24
1
'$'||TRIM(tochar(MIN(db.Account_Balance),'9,999,999'))|| ' to ' ||'$'||TRIM(tochar(max(db.Account_Balance),'9,999,999'))

gave me what I needed .. Thanks!

CoSpringsGuy
  • 1,615
  • 1
  • 13
  • 16