derived column from multiple where's

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

SQL Server 2005 Forums

Transact-SQL (2005)

derived column from multiple where's

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2010-04-27 : 07:57:28

Hi all,
this is probably a silly question but I'm just trying to get my head around it.
I have a table in SQL with say an ordertype field..
I want to create derived column called say, dept based on MULTIPLE wheres that will populate the dept column with text = eng,finance,comms
eg: select column1,column2,column3 from table..

where odertype = 1 or 2 populate dept colmn with text eng
where ordertype = 3 or 4 or 5 populate dept column with text finance
where ordertype = 1 and 3 populate dept column with text comms

can someone point me in the right direction please..
Ray..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 08:04:00

[code]UPDATE Table
SET dept= CASE
WHEN ordertype IN (1,2) THEN 'eng'
WHEN ordertype IN (3,4,5) THEN 'finance'
...
END
WHERE ordertype BETWEEN 1 AND 5
[/code]
the first and last conditions seem to be overlapping though. what do you mean 1 and 3? if it has occurance of 1 and 3 both?

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

rwaldron
Posting Yak Master

131 Posts

Posted - 2010-04-27 : 08:15:13

hiya and thx for replying
sorry 1 and 3 was a typo..missing the between , just an example

so my wheres would be like the following

where ordertype = 1 or between 2 and 5
where ordertype = 3 or between 2 and 3

thx for your help.
I'll look into your example

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-27 : 10:06:36

Can you have, say, 2.5 (or 2.7, etc.)? If not, the where clause 2 can simply be: where ordertype = 3, eliminating the between. Kind of the same with where clause 1, use: where ordertype = 1 or ordertype = 3 or ordertype = 4.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

rwaldron
Posting Yak Master

131 Posts

Posted - 2010-04-28 : 07:46:21

Thx Terry
I got sorted thanks to your example

Ray..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources