?
Solved

SQL ROUND SYNTAX FOR COMPUTATION OF AN ALIAS

Posted on 2013-12-09
6
Medium Priority
?
259 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 69

Accepted Solution

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

771 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