| Author |
Topic |
|
monotony
Starting Member
4 Posts |
 Posted - 2010-09-17 : 16:28:43
|
| Hello all,I'm trying to figure out how to make a column value based on other columns in the same table. I have a member table with the fields: memTitle, memTitleShow, memFName, memFNameShow, memLName, memCredentials, memCredentialsShow.The display is: [memTitle][memFName][memLName][, memCredentials]The columns suffixed by "Show" are bit fields. I would like the computed column to calculate the display based on the bit fields, so if memTitleShow is on, it would pull in the memTitle from the table, if ti is off, memTitle will return as blank. The only column that will show up no matter what is the memLName field. Everythign else can be turned off and off based on those bits.Can anyone help me create a formula for the Display column that will accomplish this task?Thanks in advance for any help...Jeff |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-18 : 06:00:44
|
selectcase when memTitleShow=1 then memTitle else '' end as memTitle,case when memFnameShow=1 then memFname else '' end as memFname,memLName,...from ...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
monotony
Starting Member
4 Posts |
Posted - 2010-09-20 : 16:03:45
|
| Thank you for the reply webfred. It is not exactly what I was looking for but it is a start.I understand how to write the select statement for this issue (I've written it about 25 times in various places on the site), what I'm trying to figure out is how to turn it into a computed column in the DB so I don't have to keep writing these super queries, just to display the name info. I don't understand how to turn it into a UDF or something and then make it the default value in a separate column called "memDisplayName" or something like that...I know this is possible, I just don't know the details... can you show me how to do this?Thanks!Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
monotony
Starting Member
4 Posts |
Posted - 2010-09-20 : 16:23:32
|
| X002548, did you even read my post? I know how to alter a table to add a freaking column. What I do not know is how to make that column's value be a UDF that returns a string that is constructed from the data based on other data in that record.Do you?Thanks,Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-20 : 16:29:07
|
| Take webfred's CASE expressions and add them as computed columns, you don't need to make them UDF's. |
|
|
|
monotony
Starting Member
4 Posts |
Posted - 2010-09-20 : 16:35:05
|
| Rob,I needed to construct a single column out of the several case statements, would that have worked?I figured it out using the UDF that I wrote. If anyone is looking to do something similar and needs the syntax (since I couldn't find it anywhere) it goes like this in the formula column:([dbo].[myUDFName]([myColumnArg]))Thanks all,Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-20 : 16:58:25
|
| Yes, you'd simply concatenate each CASE (example):ALTER TABLE myTable ADD NewColumn AS CASE WHEN A=1 THEN ColA ELSE '' END +CASE WHEN B=1 THEN ColB ELSE '' END +CASE WHEN C=1 THEN ColC ELSE '' END |
|
|
|
|
Constructing a column based on other columns