Author |
Topic |
pattonjo
Starting Member
11 Posts |
Posted - 2013-06-11 : 11:20:07
|
I have two columns that retain data I want to perform calculations on.(1/(CycleTime/3600)*(Percentage)*Percentage is defined as float.How can I define a column (that doesn't exist) as an expression and then perform the above operation?Thanks Much |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-11 : 11:37:18
|
Sounds that you're looking for computed column. ExampleSELECT ID,Name,Salar,Salary*12 AnnualSalaryFROM TableNameHighlighted in red, is how you can use computation in your SQL query to have a computed column at run time.CheersMIK |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 00:34:02
|
SELECT CycleTime, Percentage, (1/(CycleTime/3600)*(Percentage) AS NewColumn, <column List>FROM YourTableAssumed that CycleTime and Percentage are two column available in the table called "YourTable"NOTE: Red marked part is for specifying other columns which you have in that tableBlue marked part represents the alias name for the calculated column--Chandu |
|
|
pattonjo
Starting Member
11 Posts |
Posted - 2013-06-13 : 00:43:47
|
I use:(1/(CycleTime/3600))*(Percentage/100)and I get desired results, but how can I handle dividing by zero errors in this particular instance? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-13 : 00:48:31
|
(1/NULLIF(CycleTime/3600, 0))*(Percentage/100) -- means whenever CycleTime/3600 values becomes zero, it will return NULL as output instead of Error--Chandu |
|
|
pattonjo
Starting Member
11 Posts |
Posted - 2013-06-24 : 16:09:19
|
Thank you for the help! |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-25 : 00:37:29
|
quote: Originally posted by pattonjo Thank you for the help!
welcome--Chandu |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-26 : 11:31:41
|
Simplify(1/(CycleTime/3600)*(Percentage) AS NewColumn36E * Percentage / NULLIF(CycleTime, 0) AS NewColumn
N 56°04'39.26"E 12°55'05.63" |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-26 : 12:42:44
|
quote: Originally posted by SwePeso Simplify(1/(CycleTime/3600)*(Percentage) AS NewColumn36E * Percentage / NULLIF(CycleTime, 0) AS NewColumn
N 56°04'39.26"E 12°55'05.63"
Whether it is a simplification depends on one's perspective. At the very least, make sure a comment describing the arithmetic is included in the code for posterity.In doing floating point calculations, because of roundoff errors, this type of simplification can sometimes get you results that are slightly different from the original long-winded formula. In almost all cases that should not matter, and in any case you should not be relying on the precision to that extent; nonetheless, something to keep in mind.Another thing that I have observed is that the arithmetic that you do in queries adds very little to the resource requirements. So sometimes I keep the formulas in their original form even if they can be simplified/shortened because keeping it in the long form makes the logic clear to someone looking at it. |
|
|
|