Multiple conditions - SQL Server Forums

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

Multiple conditions

Author

Topic

sqln00b9
Starting Member

8 Posts

Posted - 2011-11-13 : 11:06:00

Hello,
I am trying to write multiple conditions but cannot get the syntax right...

here are two things i have tried:
-----------------------------------
update table
set titlelevel=1
if
(
(title like '%owner%' or title like '%president%' or title like '%CEO%' or title like '%chief exec%' or title like '%managing dir%' or title like '%C.E.O%' or title like '%founder%') and title not like '%vice%' and title not like '%admin%'
)
set titlelevel=10
if
(
(title like '%partner%' or title like '%chairman%' or title like '%proprietor%' or title like '%principal%' )
and (titlelevel is null or titlelevel='')
)
set titlelevel=20
if
(
(title like '%chief operat %' or title like '%COO%' or title like '%CIO%' or title like '%chief infor%')
and (titlelevel is null or titlelevel='')
)
----------------------------------------------

and then i tried this based on tutorials as multiple sets are obviously not going to work:

update table
set titlelevel =case FieldA
when (title like '%owner%' or title like '%president%' or title like '%CEO%' or title like '%chief exec%' or title like '%managing dir%' or title like '%C.E.O%' or title like '%founder%') and title not like '%vice%' and title not like '%admin%')then'1'
when (title like '%test%') then '2'
else '3'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 11:46:50

rather than using so many like and not like pattern why not put all of them in a inclusion and exclusion tables along with relevant level values and then use left/inner join onto table on designation values to do update. In this way solution will be scalable as if you want to add one more designation check, you just need to add a record to exclusion or inclusion table rather than keeping on adding new conditions to your where clause

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

sqln00b9
Starting Member

8 Posts

Posted - 2011-11-13 : 22:45:36

That wouldn't work as I'd have to potentially map an unlimited numer of potential titles...that's why the like. For example, if a company has someone called VP of Creative Ideology. There's no way, I can put that title in a table beforehand but with the keyword 'VP' I can identify who he might be.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 00:19:35

sorry i didnt get that. anyways you're now putting the description in like arent you? didnt understand why you cant populate it in a table instead.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources