[Imported] Rounding in a List

[sbeadle]

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: tfranklin, originally posted: 2019-10-21 23:33:42 Id:592
For some reason, this doesn't exist in ClearSCADA -- possibly just a lack of functionality in ANSI-92. There's a Ceiling and a Floor function, but no round 😕 . Has anybody found a not-so-ugly workaround? We've done some creative things in the past to skirt around it but they're all ugly solutions that eventually lead to poor sorting if somebody orders the column.

Example: CurrentValueAsReal = 78.12345 and we want to round it to 2 decimal places within a list.
Option 1 - CurrentValueFormatted -- can't use this, even if we strip out just the number portion because it'll return a string.

Option 2 - CAST(FORMATVALUE(CURRENTVALUEASREAL USING FORMAT)) AS "Something". This seems like it works, but seems less than ideal.

Option 3 - Pad the number with a bunch of empty spaces and replace the number of spaces that there are characters in the returned value. Problem, negative numbers don't sort properly since you're still sorting a string at the end of the day.

Of all of the options above, Option 2 seems like the best bet. Is this the wrong way to be going about it?

[sbeadle · Accepted solution]

Comments

hardin4019hardin4019
October 22 Flag4.59.45.65
Not sure why CurrentValueFormatted wouldn't work for you. I have some values formatted as #,### and the CurrentValueFormatted comes back as 1,586, and the CurrentValueReal is 1,586.7234. Maybe you want to do the same thing but leave the comma out and add a few decimal places?

geoffpattongeoffpatton
October 22 edited October 22 Flag99.112.237.243
I have used Option 2 with USING '0000.0' and it has worked fine for sorting. the numbers get leading 0s. Not sure about what happens with negative numbers, there aren't any, where I have used this.

tfranklintfranklin
October 22 Flag67.200.177.114
@hardin4019 said:
Not sure why CurrentValueFormatted wouldn't work for you. I have some values formatted as #,### and the CurrentValueFormatted comes back as 1,586, and the CurrentValueReal is 1,586.7234. Maybe you want to do the same thing but leave the comma out and add a few decimal places?

Can't just use CurrentValueFormatted because it's a string. Sorting of the string would result in an improper sort.

@geoffpatton said:
I have used Option 2 with USING '0000.0' and it has worked fine for sorting. the numbers get leading 0s.

Seems like what we'll end up needing to do.

geoffpattongeoffpatton
October 22 Flag99.112.237.243
@tfranklin you replied faster then I edited. I am not sure what happens to negative numbers, there aren't any where I applied this.

tfranklintfranklin
October 22 Flag67.200.177.114
@geoffpatton said:
@tfranklin you replied faster then I edited. I am not sure what happens to negative numbers, there aren't any where I applied this.

I'm seeing negatives on my side with that syntax.

SELECT
ID, FULLNAME, CURRENTVALUEASREAL, CAST( FORMATVALUE( CURRENTVALUEASREAL USING FORMAT ) AS REAL ) AS "Rounded"
FROM
CPOINTALG
WHERE
FULLNAME LIKE '%Rounding Test%'
ORDER BY
"Rounded" DESC