Solved

SQL ROUND SYNTAX FOR COMPUTATION OF AN ALIAS

Posted on 2013-12-09
6
211 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

939 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

10 Experts available now in Live!

Get 1:1 Help Now