Wednesday, April 1, 2009

Showing numbers as string while retaining the thousand separator

Greetings, programs!

Something that came up on OTN:
Cast a metric (number) as char but still have the thousand separator. E.g.: if "Sales" > 1000 then "Nothing to report" else 1'000?

Here's a formula you can use:

case when (LENGTH(cast(Fact.Sales as char)) > 12) then
(SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 15 FOR 3) || '''')
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 9) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 12 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 6) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 9 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 0) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 6 FOR 7)
else 'Nothing to report' END

End of line.

No comments:

Post a Comment