create table case when condition

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

create table case when condition

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-01-17 : 03:16:54

how can i create table on case when condition? is it possible?

age = case when DATEDIFF(YY,DOB, Date) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End,
Gender = case when Sex = 1 then 'M' Else 'F' End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 03:20:33

if you mean creating computed columns you can use
CREATE TABLE
(
...

age as case when DATEDIFF(YY,DOB, Date) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End,
Gender as case when Sex = 1 then 'M' Else 'F' End
)

and of course [Date] and Sex columns have to be declared prior to above computed columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-01-17 : 03:34:59

can you explain how it actually works?

there are no need for select statement right?

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-01-17 : 03:44:42

I got it..
THANKS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 03:46:24

quote:
Originally posted by peace

can you explain how it actually works?

there are no need for select statement right?

nope..once you include it in create table as above

you dont have to insert any values in them as values will be automatically placed as per your expression

see this example


CREATE TABLE ABC
(
Name varchar(10),
DOB datetime,
Sex bit,
age as case when DATEDIFF(YY,DOB, getdate()) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End, 
Gender as case when Sex = 1 then 'M' Else 'F' End
)

insert ABC (Name,DOb,Sex)
values('John','19870221',1),
('Mary','19780516',0),
('Joseph','19220103',1)


SELECT * FROM ABC



output
-----------------------------------------
Name	DOB	Sex	age	Gender
-----------------------------------------
John	1987-02-21 00:00:00.000	1	Unknown	M
Mary	1978-05-16 00:00:00.000	0	Unknown	F
Joseph	1922-01-03 00:00:00.000	1	Senior Citizen	M

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-17 : 03:51:02

Age and Gender will be populated by using DOB and sex columns
See the example...

CREATE TABLE testCase
(
id int,
name varchar(10),
DOB date,
sex int,
age as case when DATEDIFF(YY,DOB, getDate()) between 50 and 100 then 'Senior Citizen' Else 'Unknown' End, 
Gender as case when Sex = 1 then 'M' Else 'F' End
)

--Observe this. you are NOT providing data for age and Gender columns
insert into testCase(id, name, DOB, sex) values(1, 'aaa', '1953-10-27', 2)

--Here you can see data for age and Gender columns
SELECT * FROM testCase

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources