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 |
|
parrot
Posting Yak Master
132 Posts |
 Posted - 2008-09-27 : 13:16:40
|
| Is there anyway to add a user defined field on a database that receives its value based on the contents of an existing field on the database? For example, I wish to add a user defined field called Gender that will have a value of 'Male' if the Sex field on the database has a value of 'M' or have a value of 'Female' if the Sex field has a value of 'F'. In effect, I wish to install conversion procedures for a field rather than having to code a CASE command in each SQL query. Can this procedure be built into the definition of a field when defining it to the database so all I have to do is include the field on a query and the conversion will happen automatically?Dave |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-27 : 13:46:37
|
| one thing you can do is to create a new column called gender with check clause for value to be male or female. then use a trigger to update value based on value of sex field for each insert,update. |
 |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-09-27 : 14:25:03
|
| That means that the Gender description will have to be stored on the database for each record rather than determining it at run time. That adds significantly to the size of the database if I want to do this for other similar fields.Dave |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-27 : 14:36:32
|
quote:
Originally posted by parrot
That means that the Gender description will have to be stored on the database for each record rather than determining it at run time. That adds significantly to the size of the database if I want to do this for other similar fields.Dave
else solution is to use case when to get value directly in select queries. |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-09-27 : 15:03:26
|
| I am programming a user front end code to automatically generate SQL code for queries submitted by a user. They don't know SQL and I am attempting to generate code for their adhoc requests. That means I would have to test for each field that I want to convert and then insert the CASE logic in the SQL query programmatically. I wanted SQL to be smart enough to contain a pre-written function for these fields but evidently there is no such thing.Dave |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-27 : 15:08:51
|
quote:
Originally posted by parrot
I am programming a user front end code to automatically generate SQL code for queries submitted by a user. They don't know SQL and I am attempting to generate code for their adhoc requests. That means I would have to test for each field that I want to convert and then insert the CASE logic in the SQL query programmatically. I wanted SQL to be smart enough to contain a pre-written function for these fields but evidently there is no such thing.Dave
what ever you do needs to be stored at database. there's no other way to make conversion on the fly. |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-09-27 : 15:37:50
|
| Thanks for your comments. Now I know what I have to do.Dave |
|
|
|
|
|
|
Defining a function for a data field