| Author |
Topic |
|
pnasz
Posting Yak Master
101 Posts |
 Posted - 2010-10-13 : 07:24:08
|
| In my table i have two computed column which is varcharMacTotalHR EMPTotalHR overtime10:20:15 06:10:00 I want another computed column which will find the difference between macTotalHR(computed column) and EmpTotalHr(Computed Column) and display the result in the computed column as (04:10:00)please help me if possible |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-13 : 08:02:47
|
You can't use computed columns when creating a new computed column. You will to go to the same source column(s).
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
gmarut
Starting Member
8 Posts |
Posted - 2010-10-14 : 11:09:16
|
| Yes you can, wrap the entire select inside another select and you can reference it.SELECT *, new_computed_column(MacTotalHR, EMPTotalHR)FROM( SELECT MacTotalHR, EMPTotalHR, ... FROM your_table) tblNew |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-14 : 11:13:55
|
quote:
Originally posted by gmarut
Yes you can, wrap the entire select inside another select and you can reference it.SELECT *, new_computed_column(MacTotalHR, EMPTotalHR)FROM( SELECT MacTotalHR, EMPTotalHR, ... FROM your_table) tblNew
Do you know what a computed column is?http://msdn.microsoft.com/en-us/library/ms191250.aspxCODO ERGO SUM |
|
|
|
gmarut
Starting Member
8 Posts |
Posted - 2010-10-14 : 11:29:27
|
| yes, and if you plug in your computations it will work. I don't know how "MacTotalHR" and "EMPTotalHR" were computed so i just referenced them as normal columns in my example. I also just substituted the "new_computed_column" computation with an arbitrary function.SELECT *, (MacTotalHR - EMPTotalHR) AS new_computed_columnFROM(SELECT 1+1 AS MacTotalHR, 2+2 AS EMPTotalHR, ...FROM your_table) tblNewObviously replace "1+1" and "2+2" with w/e your computations are. Better? |
|
|
|
gmarut
Starting Member
8 Posts |
Posted - 2010-10-14 : 11:39:55
|
| Are you saying that when referencing "MacTotalHR" you are not seeing "10" (as in this example)? The query below should return the following results:MacTotalHR | EMPTotalHR | Difference10 | 4 | 6SELECT *, (tblNew.MacTotalHR - tblNew.EMPTotalHR) AS [Difference]FROM(SELECT 5+5 AS [MacTotalHR], 2+2 AS [EMPTotalHR], ...FROM your_table) tblNew |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-14 : 11:42:22
|
quote:
Originally posted by gmarut
yes, and if you plug in your computations it will work. I don't know how "MacTotalHR" and "EMPTotalHR" were computed so i just referenced them as normal columns in my example. I also just substituted the "new_computed_column" computation with an arbitrary function.SELECT *, (MacTotalHR - EMPTotalHR) AS new_computed_columnFROM(SELECT 1+1 AS MacTotalHR, 2+2 AS EMPTotalHR, ...FROM your_table) tblNewObviously replace "1+1" and "2+2" with w/e your computations are. Better?
Did you read this in the link I posted?"A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery"My impression is still that you do not know what a computed column is. It is not a column returned by a query; it is a column in a table that is a computed experssion that appears to the user as a column.Or just post a sample CREATE TABLE statement that illustrates using a computed column in another computed column.CODO ERGO SUM |
|
|
|
gmarut
Starting Member
8 Posts |
Posted - 2010-10-14 : 11:46:18
|
| Ah, im sorry. I misunderstood the question |
|
|
|
|
Computed column