Field population via formula

[david7777777777]

I read this somewhere but I can't find it. I need to populate the Floor field based on the contents of the DataPort field in the tbl_employees table. The scheme is simple enough. If the DataPort is 8112, I want the first digit "8" to be copied into the Floor field. I always want the first digit in the DataPort field to be copied into the Floor field. Thanks.

 

[rac2]

UPDATE tbl_employees SET Floor = SUBSTRING(DataPort, 1, 1)

 

[david7777777777]

I'm typing this code into the Formula property field for the Floor field in the tbl_employees table and when I try to exot the field SQL tells me "Error validating the formula for column 'Floor'

 

[redlam]

Hi.
If you are adding this computed column to an existing table, you could do something like this:

alter table tbl_employees
add [floor] as left(dataport,1)

If you are creating the table for the first time, it would look like this:

create table tbl_employees
(dataport int,
[floor] as left(dataport,1))

 

[david7777777777]

I'm modifying an existin table. Where exactly should I be placing this code anyway? I'm working in Design view in Enterprise Manager, SQL 2000.

 

[redlam]

... And if you're just typing a formula into the formula property of the tbl_Employees table, it would look like this:

(left([dataport],1))

 

[david7777777777]

It doesn't like that either....

 

[tlbroadbent]

NOTE: FLOOR is a reserved word in SQL. It is the name of a SQL function. You should use a different name for your column. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[redlam]

Huh. Seems to work OK for me. Do you have data in this table already? Can you drop the Floor field and Add it again? If so, try running this code from Query Analyzer:

alter table tbl_employees
drop column [floor]
alter table tbl_employees
add [floor] as left(dataport,1)

If you get an error, post it here so we can see what's wrong.

 

[tlbroadbent]

You can use reserved words but it is good practice to not use reserved words as colulmn names. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[david7777777777]

I'm the kind of person who would prefer not to use a reserved word, just in case. It creates too manu unknowns, and most of this stuff already has enough unknowns right out of the box. Thanks. I'll let you know what happens. I'm going to delete that column and create another one with a different name like BuildingFloor.

 

[redlam]

Just clarifying:
"Huh. Seems to work for me..." was in response to David's post "It doesn't like that either....". I'm in agreement on the reserved word situation. If you have the freedom to change the field names, I would also recommend doing so. If you use a reserved word, you always have to use square brackets [] when referring to it.

Let us know if you need more help with computed columns!

 

[david7777777777]

Ok redlam, I did your alter table thing and it worked. But how do I create the field with a char data type instead of the varchar type?

 

[redlam]

Ya got me there. I don't believe you can control the datatype of a computed column but I'm not positive either way. I searched a bit through BOL and didn't find anything - sorry.