Author 
Topic 

AskSQLTeam
Ask SQLTeam Question
USA
0 Posts 
Posted  02/12/2007 : 21:55:10

Jan writes "Is it possible to have in your database a column that is automatically the results of another column. Here is my problem, I am using a report writer that does not allow BIT data types. I need to use the data found in the BIT column, so I am wondering if I can create a column that triggers its values from another column in the DB. My new column type will be TINYINT. Then in my report I will use the values in new column that contiains the values from the BIT column. Any help or suggestions would be appreciated." 

khtan
In (Som, Ni, Yak)
Singapore
17682 Posts 
Posted  02/12/2007 : 22:02:00

is both the columns on the same table ? why not use computed column ?
KH



jsorenso
Starting Member
USA
3 Posts 
Posted  02/13/2007 : 14:28:31

Both columns are in the same table. Will a computed column make column B column A? 


SwePeso
Patron Saint of Lost Yaks
Sweden
30282 Posts 
Posted  02/13/2007 : 15:06:05

Yes.
quote: Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:
Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.
A computed column cannot be the target of an INSERT or UPDATE statement.
Peter Larsson Helsingborg, Sweden 


jsorenso
Starting Member
USA
3 Posts 
Posted  02/14/2007 : 10:17:32

My only problem with a computed column is the fact that when I set column B to Column A, Column B mimics Column A in type. I need Column B to be a tinyint where as column A is a BIT type. If I set as the formula for column B, Column A, then I get a BIT type for Column B. I need Column B to be TinyInt. Can a formula be constructed such that if I set column B to column A the column type can change to something else? 


khtan
In (Som, Ni, Yak)
Singapore
17682 Posts 
Posted  02/14/2007 : 10:28:29

you can use convert() to convert it in the formula
KH



jsorenso
Starting Member
USA
3 Posts 
Posted  02/14/2007 : 17:08:52

Brilliant solution, the computed column in conjunction with cast() worked. It was simple, elegant and truely a "Flowing Fount of Yak Knowledge" Thanks all.




Topic 
