Solved

SQL ROUND SYNTAX FOR COMPUTATION OF AN ALIAS

Posted on 2013-12-09
6
208 Views
Last Modified: 2013-12-10
Ok, I do not know why I am having syntax issues with the ROUND statement!  I have the following:

,[RENTAL_COST_FW]
      ,[DAYS_BILLED_FW]
      ,[MILEAGE_RATE_FW]
      ,(([COST_LABOUR_FW] + [COST_PARTS_FW] + [CVR_FEE_FW] + [TOTAL_TAX_FW])) as         FW_COST_TOTAL

I want to round the alias "FW_TOTAL_COST" to 2 decimal places... I keep getting a syntax error when I put the ROUND clause in.
0
Comment
Question by:66chawger
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39707031
>I keep getting a syntax error when I put the ROUND clause in.
Show us the T-SQL that does this, and what is the data type (float, numeric, etc.) of this column?

>I want to round the alias "FW_TOTAL_COST" to 2 decimal places...
I don't see the column FW_TOTAL_COST anywhere in the above T-SQL block, (FW_COST_TOTAL, same thing?), so show us that too.
0
 

Author Comment

by:66chawger
ID: 39707096
Jim,

Yes, you are correct, my fault... it is FW_COST_TOTAL
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39707103
Give this code fragment a whirl..

,ROUND([COST_LABOUR_FW] + [COST_PARTS_FW] + [CVR_FEE_FW] + [TOTAL_TAX_FW],2) as         FW_COST_TOTAL

Open in new window

... and if it fails, copy-paste in this question the entire query, and the error message
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39707291
,CAST([COST_LABOUR_FW] + [COST_PARTS_FW] + [CVR_FEE_FW] + [TOTAL_TAX_FW] AS decimal(9, 2)) as
FW_COST_TOTAL

/*
CAST will automatically ROUND, as it changes the data type of the final result to have only 2 decimal places.  If you want to do computations with that value, it might be important for it to have only 2 decimal places if that is what the computation expects it to have.
*/
0
 

Author Closing Comment

by:66chawger
ID: 39709480
Scott, this is it.  Cast didn't even cross my mind.. must have been in la la land at the time!  Each variable (column) used in the computation is defined with 4 decimal places.  What you gave me serves the purpose as I am working with 3rd party software, so this will keep me from having to physically change the attributes in the table.

Jim, thanks for your response, that syntax worked, but you had the round for one of the variables, not the alias.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39709598
>but you had the round for one of the variables, not the alias.
Curiosity overwhelms me ... what exactly do you mean by that?
The below code looks like four columns, not variables, and aliases are for naming only and do not participate in expressions, unless we're talking a subquery/cte..
,ROUND([COST_LABOUR_FW] + [COST_PARTS_FW] + [CVR_FEE_FW] + [TOTAL_TAX_FW],2) as         FW_COST_TOTAL

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now