Adding a field whose value DEPENDS on other fields in table

Hi,
Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
another field for Status (a generic field) , which has value :
1.Open if the value of the field OPEN_STATUS=T ,
2.InProcess if the value of the field INPROCESS_STATUS=T
3.Completed if COMPLETED_STATUS=T.
Is there a way to do this ?...maybe by writing these if statements? How can
this be done? I know this is not a good desgin but already many applications
are using these 3 fields which I dont want to change... So is there any way
of creating this new field in the same table based on the criteria specified
above?
Thanks for any help.
--
pmudTry creating a calculated column.
alter table t
add status as case when OPEN_STATUS='T' then 'Open' when
INPROCESS_STATUS='T' then 'InProcess' when COMPLETED_STATUS='T' then
'Completed' else 'UNKNOWN' end
go
Are those columns exclusive?, because the expressions inside CASE will be
evaluated in the same order as they appear.
AMB
"pmud" wrote:
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud|||You might also consider changing the name of the table. then create a view
with the original table name and include the special column in the view...
This will insulate you and theprogrammers from future changes.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9176C167-F9FB-48D4-AA84-6DA690D02B76@.microsoft.com...
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to
> add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How
> can
> this be done? I know this is not a good desgin but already many
> applications
> are using these 3 fields which I dont want to change... So is there any
> way
> of creating this new field in the same table based on the criteria
> specified
> above?
> Thanks for any help.
> --
> pmud|||the solution using a caculated field is good if the field does not have to be
indexed or searched against (slow).
If the field has to be indexed or searched and the table has lots of rows,
consider using a trigger to calculate and store the value.
"pmud" wrote:
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud