Solved

SQL ROUND SYNTAX FOR COMPUTATION OF AN ALIAS

Posted on 2013-12-09
6
249 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
[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
  • 3
  • 2
6 Comments
 
LVL 66

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 66

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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 69

Accepted Solution

by:
Scott Pletcher 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 66

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 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