question

enterprisetoday avatar image
enterprisetoday asked

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

sqlformatting
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Since this is all about numeric format rather than value, could you replace the scale column it’s a formatString column, something like ‘##########.00’, and use that with the format() function? select format(qty, formatString) as Qty

2 Likes 2 ·

Can you format the values in the presentation layer - i.e. the end user application?

1 Like 1 ·

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

0 Likes 0 ·

1 Answer

· Write an Answer
enterprisetoday avatar image
enterprisetoday answered

@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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.