SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Triggering one column to equal another column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/12/2007 :  21:55:10  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
17627 Posts

Posted - 02/12/2007 :  22:02:00  Show Profile  Reply with Quote
is both the columns on the same table ? why not use computed column ?


KH

Go to Top of Page

jsorenso
Starting Member

USA
3 Posts

Posted - 02/13/2007 :  14:28:31  Show Profile  Reply with Quote
Both columns are in the same table. Will a computed column make column B column A?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 02/13/2007 :  15:06:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

jsorenso
Starting Member

USA
3 Posts

Posted - 02/14/2007 :  10:17:32  Show Profile  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 02/14/2007 :  10:28:29  Show Profile  Reply with Quote
you can use convert() to convert it in the formula


KH

Go to Top of Page

jsorenso
Starting Member

USA
3 Posts

Posted - 02/14/2007 :  17:08:52  Show Profile  Reply with Quote
Brilliant solution, the computed column in conjunction with cast() worked. It was simple, elegant and truely a "Flowing Fount of Yak Knowledge" Thanks all.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000