using if in computed columns expressions

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

using if in computed columns expressions

Author

Topic

AurasD
Starting Member

23 Posts

Posted - 2009-01-11 : 06:21:00

Hello!I'm new Sql Server 2005 (i have read a book about it)and i have the following question:
I have a database with 3 columns:PersID,PersLocation,PersLocCode.Now the thing is that PersLocCode is a value that depends of PersLocation.For example if PersLocation has a value of Dambovita the PersLocCode is DB,if PersLocation is Arges the PersLocCode is AG.Now i have 26 PersLocation's.I want to make the PersLocCode to be a computed column so that if i enter for the PersLocation the value Dambovita the database enter in the PersLocCode the value DB.So i think i would have to include some kind of if or case in the PersLocCode computed column expression that could compare the 26 cases of the PersLocation possible values and assign the corespondent code in the PersLocCode.On what ways can i do this? Thank you!

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-11 : 06:28:55

yes ur right. Define the computed column definition using case stmts for those 26 persLocations.

read this link

http://balajiramesh.wordpress.com/2008/06/05/computed-columns-in-sql-server-2005/

AurasD
Starting Member

23 Posts

Posted - 2009-01-11 : 06:43:52

Thank you for the quick reply.But i see in that article that you still have to pass a value for the computed value.I was thinking that the computed value wouldn't have to appear in the insert since it's value was set accordingly to the value in the PersLocation.So in my case:
CREATE TABLE Persoane (
PersID INT,
PersLocation NVARCHAR(30),
operator CHAR,
PersLocCode AS CASE operator
WHEN PersLocation='Dambovita' THEN 'DB'
...
...
END
PERSISTED
) ;
But my PersLocCode is not an operator it's a column.So what do i do?
I haven't used computed columns of this complexity till now so i don't know how to use case,if,etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-11 : 06:59:46

quote:
Originally posted by AurasD

Thank you for the quick reply.But i see in that article that you still have to pass a value for the computed value.I was thinking that the computed value wouldn't have to appear in the insert since it's value was set accordingly to the value in the PersLocation.So in my case:
CREATE TABLE Persoane (
PersID INT,
PersLocation NVARCHAR(30),
operator CHAR,
PersLocCode AS CASE operator
WHEN PersLocation='Dambovita' THEN 'DB'
...
...
END
PERSISTED
) ;
But my PersLocCode is not an operator it's a column.So what do i do?
I haven't used computed columns of this complexity till now so i don't know how to use case,if,etc.

you dont have to explicitly insert values for computed columns, based on defined calculation it sets value automatically based on valued inserted for dependent columns

AurasD
Starting Member

23 Posts

Posted - 2009-01-11 : 07:35:40

Guys I've tried this:


create table Persoane(
PersID int,
PersLocation nvarchar(30),
PersLocCode nvarchar(2) as case operator
when PersLocation='Dambovita' then 'DB'
when PersLocation='Arges' then 'AG'
persisted
);

but i get the following error:


Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.
So what is the correct syntax for this to work?I really can't figure it out on my own.Thanx

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-11 : 07:36:19

quote:
Originally posted by AurasD

Thank you for the quick reply.But i see in that article that you still have to pass a value for the computed value.I was thinking that the computed value wouldn't have to appear in the insert since it's value was set accordingly to the value in the PersLocation.So in my case:
CREATE TABLE Persoane (
PersID INT,
PersLocation NVARCHAR(30),
PersLocCode AS CASE operator
WHEN PersLocation='Dambovita' THEN 'DB'
...
...
END
PERSISTED
) ;
But my PersLocCode is not an operator it's a column.So what do i do?
I haven't used computed columns of this complexity till now so i don't know how to use case,if,etc.

Create ur table in this way ...

CREATE TABLE Persoane (
PersID INT,
PersLocation NVARCHAR(30),
PersLocCode AS CASE PersLocation
WHEN 'Dambovita' THEN 'DB'
WHEN 'Arges' THEN 'AG'
...
...
END
PERSISTED
) ;

No need to insert into computed column. Sql server itself calculates based on definition u had given for that and stores in that column.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-11 : 07:42:48

quote:

Originally posted by AurasD

Guys I've tried this:


create table Persoane(
PersID int,
PersLocation nvarchar(30),
PersLocCode nvarchar(2) as case operator
when PersLocation='Dambovita' then 'DB'
when PersLocation='Arges' then 'AG'
persisted
);

but i get the following error:


Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.
So what is the correct syntax for this to work?I really can't figure it out on my own.Thanx

Don't mention the datatype of computed column and Here there is no operator column in ur table so case operator should be changed to case PersLocation as PersLocCode depends on PersLocation ...

For correct syntax
try the solution which i posted at 01/11/2009 : 07:36:19

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-11 : 07:58:25

quote:
Originally posted by AurasD

Thank you for the quick reply.But i see in that article that you still have to pass a value for the computed value.I was thinking that the computed value wouldn't have to appear in the insert since it's value was set accordingly to the value in the PersLocation.So in my case:
CREATE TABLE Persoane (
PersID INT,
PersLocation NVARCHAR(30),
operator CHAR,
PersLocCode AS CASE operator
WHEN PersLocation='Dambovita' THEN 'DB'
...
...
END
PERSISTED
) ;
But my PersLocCode is not an operator it's a column.So what do i do?
I haven't used computed columns of this complexity till now so i don't know how to use case,if,etc.

Operator is a column in a table which is used in example( In link i provided above). Reread the content in the Link which i provided earlier.

AurasD
Starting Member

23 Posts

Posted - 2009-01-11 : 08:11:13

OMG!!!!!!!!!!!!!!!
It works so flawlessly !!!
raky i have only one thing to say:
DUDE..U ROCK!!!
Well thanx you little genius

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-11 : 08:30:11

Why use a computed column?
Add a new table and join to that table when it's necessary to display the PersLocCode.

E 12°55'05.63"
N 56°04'39.26"

AurasD
Starting Member

23 Posts

Posted - 2009-01-11 : 09:32:52

Well I'm using C# to program the interface and I have a datagridview for each of my tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources