October 20, 2011 at 11:23 am
Hello,
I have a report I'm building for a Steel Manufacturer that involves certain calculations. My dataset is fine until I go to round the calculations. I keep getting the converting varchar to float error. I converted and rounded all the values that make up the calculation, but everything I try still gives me this error. What I want to do is to get the sum of the four elements into a decimal to meet the criteria. As of right now it gives me a calcualtion, I just can't round it to the decimal place that is desired. Here is the code I'm using so far...any help would be greatly appreciated.
Thank you,
Joe
USE [EAF]
select
WSHHeatNumberID,
Grade,
Convert(Float, Round(Carbon,2)) AS C,
Convert(Float, Round(Nickel,2)) AS Ni,
Convert(Float, Round(Chromium,2)) AS Cr,
Convert(Float, Round(Molybdenum,3)) AS Mo,
Copper+Nickel+Chromium+Molybdenum AS CNCM
From HeatChemistryFinal
October 20, 2011 at 11:26 am
Can you please post the DDL for the table?
Thanks,
Jared
Jared
CE - Microsoft
October 20, 2011 at 11:32 am
However, my first guess is that you are trying to round a varchar. Imagine trying to do this:
SELECT ROUND('jared')
I wouldn't do ROUND until your final calculation. However, for the requirements you may have to do it beforehand, and I understand that. Also, test the difference between CAST and CONVERT on performance is that is an issue.
Thanks,
Jared
Jared
CE - Microsoft
October 20, 2011 at 11:36 am
The data for the elements all come from the HeatChemistry table. By DDL, do you mean a stored procdure? So far, I just have this as a normal query.
Joe
October 20, 2011 at 11:38 am
I will try CAST and see if that changes anything, thanks,
Joe
October 20, 2011 at 11:43 am
DDL = Data Definition Language.
i.e. your create table scripts.
October 20, 2011 at 11:45 am
I'm sorry... Maybe you misunderstand. CAST vs CONVERT would only be for performance.
Do this:
SELECT ROUND(CAST(ColumnName AS FLOAT),2) FROM Table
Thanks,
Jared
Jared
CE - Microsoft
October 20, 2011 at 11:51 am
jared-709193 (10/20/2011)
I'm sorry... Maybe you misunderstand. CAST vs CONVERT would only be for performance.Do this:
SELECT ROUND(CAST(ColumnName AS FLOAT),2) FROM TableThanks,
Jared
More specifically like this:
USE [EAF]
select
WSHHeatNumberID,
Grade,
Round(CAST(Carbon AS FLOAT),2) AS C,
Round(CAST(Nickel AS FLOAT),2) AS Ni,
Round(CAST(Chromium AS FLOAT),2) AS Cr,
Round(CAST(Molybdenum AS FLOAT),3) AS Mo,
Round(CAST(Carbon AS FLOAT),2)+Round(CAST(Nickel AS FLOAT),2)+Round(CAST(Chromium AS FLOAT),2)+Round(CAST(Molybdenum AS FLOAT),3) AS CNCM
From HeatChemistryFinal
You see... You cannot ROUND a VARCHAR, which is your element's information. It really should be stored as a FLOAT. So, you have to convert/cast the VARCHAR before you can round it. You were fetting the error because of the ROUND, not because of the CONVERT.
Lastly, you are just doing it to the returned data, not to tha actual data. Which means when you sum them up, you cannot reference the column alias.
Thanks,
Jared
Jared
CE - Microsoft
October 20, 2011 at 12:03 pm
Wow, that was exactly what I was looking for. Thanks for all your help. That gave a different way to think about how I'm going to put my overall query together, but this one piece was giving me fits. Thanks Again,
Joe
October 20, 2011 at 12:06 pm
Awesome! FYI... I tested the CAST vs CONVERT. Use CONVERT for each one to increase performance over the CAST. So:
ROUND(CONVERT(FLOAT,ColumnName),2)
🙂
Jared
Jared
CE - Microsoft
October 20, 2011 at 12:09 pm
Sounds Good,
Thanks,
Joe 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply