Formatting numeric values to a predetermined scale
Hello,
I would like the option of choosing a precision scale for numeric values. My database holds most values as numeric(10,3) however I propose to have the customer choose how many decimal places they want to see. The following code illustrates my issue:
DECLARE @RET TABLE (BATCH_NUMBER VARCHAR(100),
QTY numeric(10,3),
UOM varchar(10),
scale int
)
INSERT INTO @RET
SELECT '12345','3.500','EA',2
I tried the following,
CASE scale
WHEN 0 THEN cast(qty AS numeric(10,0))
WHEN 1 THEN cast(qty AS numeric(10,1))
WHEN 2 THEN cast(qty AS numeric(10,2))
ELSE cast(qty AS numeric(10,3))
END as 'Qty'
However this style will always choose the highest amount of precision, regardless of the scale value. It's the same if i try 'WHEN scale = 0' syntax style.
This option below works, when qty is a float,
round(cast (qty AS float) * power(10, scale), 0) / power(10, scale) AS 'Qty',
However it will drop trailing zero's, and It would be important that values displayed line up correctly. Is there a better way to implement a formatting strategy?
Thanks, Dallas
Can you format the values in the presentation layer - i.e. the end user application?
Sure I certainly considered that option. However that comes at at a deployment and distribution cost (releasing new software onto mobile devices remotely). there's also the implementation as these fields are used across a variety of controls in the customer facing application - some time consuming work!
So given the opportunity to implement this logic in the backend, i jumped at that chance.
Dallas
Best Answer
@KenJ's solution has worked out well for this situation.
format(qty, 'N' + cast(coalesce(scale,3) as char(1))) AS 'QTY'
Thank you for your suggestion!
Dallas
