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 |
|
andrewcw
Posting Yak Master
133 Posts |
 Posted - 2009-10-17 : 00:52:31
|
| I have a field where I need to add a new field based on the first 4 characters of field_A. I only have 5 items that can be associated with the first 4 characters of field_A and there will always be a match ( no null or other). I need to make this association. Is making a new table with an inner join my only option ? The data is in the field_A and other fields is from a rollup of a 3 level query.New_Master_Field | field_ABOLT 1234678NUT 3756987Thanks ...andrewcw |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-17 : 00:56:25
|
field_A is a varchar ?select left(field_A, 4)from yourtable
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
andrewcw
Posting Yak Master
133 Posts |
Posted - 2009-10-17 : 09:49:38
|
| Yes field_A is a varchar. I am familiar with setting a constant like thisSelect columnA, columnN, 'fred' as columnCBut what options do I have when the new field value will vary as the contents of the Field_A ? Perhaps 5 unions ?? each based on the where left(field_A, 4)='1234', etc ? like this:select 'BOLT' as new_masterField, Field_A form { } where left(field_A, 4)='1234' UNION select 'NUT' as new_masterField, Field_A form { } where left(field_A, 4)='3756' etcIs there any better way to set the value ? THANKSandrewcw |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-17 : 10:34:20
|
[code]selectcase left(field_A,4) when '1234' then 'BOLT' when '3756' then 'NUT' when '6666' then 'BEAST' ELSE 'Do not know'end as new_masterField,Field_Afrom table[/code]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
andrewcw
Posting Yak Master
133 Posts |
Posted - 2009-10-17 : 15:38:08
|
| Thanks !!!!andrewcw |
|
|
|
|
|
|
|
|
Fill a field