Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kigroy
Starting Member
8 Posts |
 Posted - 2011-01-10 : 00:49:14
|
| I need to find the syntax for creating two derived columns.1) I want to a create table and create the derived column from two columns within that table. Specifically, I need to substract dateField1 from dateField2 and call it column "duration". 2) For the second column I want to create a derived column, either while creating the table or alter the table after it was created, that is comprised of multiplying a field from another table by 2. ie. in table1 I want to a field called deposit and have it reference table table2's column rent. All help is very much appreciated...Thanks. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-10 : 01:59:42
|
| Following is an example:Select Col1,Col2,Col1+Col2 as DerivedColumn1,Col1*Col2 as DerivedColumn2 from Table1This is how you can do it :) |
 |
|
|
kigroy
Starting Member
8 Posts |
Posted - 2011-01-10 : 02:09:33
|
| Thanks MIK!I'll test it in the morning and post my result. Off to bed for now... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-10 : 04:11:48
|
| Just a clarification: if you want this column to be a part of your table it's called a "computed" column which means that the column is automatically created based on the values of one or more other columns. During a select from that table the computed column will look and be queried just like any other column.A derived column is a part of a select just like MIK_2008 posted.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
kigroy
Starting Member
8 Posts |
Posted - 2011-01-10 : 16:27:18
|
quote:
Originally posted by Lumbago
Just a clarification: if you want this column to be a part of your table it's called a "computed" column which means that the column is automatically created based on the values of one or more other columns. During a select from that table the computed column will look and be queried just like any other column.A derived column is a part of a select just like MIK_2008 posted.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com
Thanks for the clarification Lumbago. As it turns out it was Computed Columns that I was trying to create not derived columns. Thanks all the same though MIK. Okay, I have the first part of my question answered. I was able to easily find the syntax for creating a computed column using a column that is in the table that I'm modifying.Now how do I create a computed column using a column from another table. Here is my code:ALTER TABLE lease ADD deposit AS (SELECT rent FROM lease, propertyForRent WHERE lease.propertyNo=propertyForRent.propertyNo);This is the error I receive:Error at line 1: ORA-00936: missing expressionNote: The columns and tables do actually exist. I have checked that. Thanks... |
|
|
|
kigroy
Starting Member
8 Posts |
Posted - 2011-01-10 : 16:40:55
|
| I'm closing this thread and starting a new that is appropriately named now that I know what I'm looking for.Thanks... |
|
|
|
|
|
|
Derived Columns