Solved

Using a variable to limit the decimal places

Posted on 2016-10-18
10
79 Views
Last Modified: 2016-10-18
I have a numeric data field (Numeric(18,6)) This is concatenated into a string with some other text values.

Trouble is not all clients want 6 decimal places after the int. I have tried Round() but his retains the 6 decimal places. Converting to a varchar does the job, but how to I make it a variable from probably 2 to 6 in length.

I have a setting table where this value is stored.

Andy
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41847866
to make your system more robust, just try to store the values in 6 decimal points, and when you want to output it, read the setting table and format the value accordingly.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 41847876
Hi Ryan - this is what I'm doing the number is stored to 6dp, and I'm asking how to display a lower dp based on my setting table.

Andy
0
 
LVL 34

Expert Comment

by:ste5an
ID: 41847877
Just do it in the front-end application/consumer.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 3

Author Comment

by:Andy Green
ID: 41847887
Hi ste5an - not able to do in front end easily.

This is someone else's code and a bit convoluted. The string is concatenated in SQL, and then parsed through an XML interpreter for the final display. Would take longer than I have to pick apart the XML and apply a transform.

For now I can change the stored proc to use Varchar(4) but not ideal.

Andy
0
 
LVL 52

Assisted Solution

by:Ryan Chong
Ryan Chong earned 100 total points
ID: 41847895
this is what I'm doing the number is stored to 6dp, and I'm asking how to display a lower dp based on my setting table
you can always do that in front-end as what already mentioned by ste5an, or you can create a stored procedure, in which it reads the setting table and then customize the values if necessary.

try customize:
CREATE PROCEDURE  [dbo].[getRecords]
AS
BEGIN
	 SET NOCOUNT ON;  	
	
	 DECLARE @SQL varchar(500
	 DECLARE @DecimalPts VARCHAR(20)
	 
	 SET @DecimalPts = (SELECT Value FROM Setting WHERE SettingField = 'DecimalPoints')
	 
     SET @SQL = 'SELECT *, COnvert(decimal(18, '+@DecimalPts+'), MyField1) FROM Table1'
	
	 EXEC(@SQL);
END

Open in new window

0
 
LVL 34

Expert Comment

by:ste5an
ID: 41847906
There is a nice term called "technical debt".. imho you have to deal with it.

This is someone else's code and a bit convoluted. The string is concatenated in SQL, and then parsed through an XML interpreter for the final display. Would take longer than I have to pick apart the XML and apply a transform.

Why concatenating it first, when you later have to pick it apart? Sounds - well - pretty unusual.
0
 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 400 total points
ID: 41847973
Round does, and should work (see example below). I guess, you may not be passing the precision parameter to the ROUND() function which is why it may not be working in your case.

Of course, in order to concatenate it, you will need to cast it back to VARCHAR/NVARCHAR.

USE tempdb;
GO
DECLARE @valueOfPi DECIMAL(18,6);
DECLARE @requiredDecimals INT = 2;

SELECT @valueOfPi = PI();

SELECT @valueOfPi AS ActualValue, 
       ROUND(@valueOfPi, @requiredDecimals) AS RoundedValue, 
       CAST(ROUND(@valueOfPi, @requiredDecimals) AS VARCHAR(20)) AS StringRepresentation;

/* RESULTS
ActualValue  RoundedValue  StringRepresentation
------------ ------------- --------------------
3.141593     3.140000      3.140000
*/

Open in new window

0
 
LVL 3

Author Comment

by:Andy Green
ID: 41847991
Thanks Nakul - but as you can see from your example Round() still displays 6 decimals padded with zeros, I'd be looking for a result of 3.14.

ste5an - the SQL is quite long so would take some working out to use your approach, but does seem to be the only way.

I know technical debt, and we have a ton of it, from varchars with no specified lengths, unnecessary cursors, even no Using statements in the front end classes to auto clean up.

Its a bit of a mess, with urgent changes required and no idea what the code is doing - ho hum...
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41848189
You'll need to truncate the converted value so it will get rid of the 0's. Check the LEFT function below:
DECLARE @MyValue Numeric(18,6)=RAND()
DECLARE @MyString VARCHAR(100)='The number is:'
DECLARE @DecPlaces INT = 5

DECLARE @MyCastValue VARCHAR(20) = CAST(ROUND(@MyValue, @DecPlaces) AS VARCHAR)

SET @MyCastValue = LEFT(@MyCastValue, CHARINDEX('.', @MyCastValue)+@DecPlaces)
SET @MyString = @MyString + @MyCastValue

PRINT @MyCastValue
PRINT @MyValue
PRINT @MyString

Open in new window

0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 41848285
Thanks Guys. All sorted now.

A
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question