How to use 'IN' keyword in Case statement

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 2000 Forums

Transact-SQL (2000)

How to use 'IN' keyword in Case statement

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2006-08-29 : 09:23:47

Hi guys,

I need to do something like

CASE sil.revenuecode
WHEN 622773258760115409 THEN 'Consultancy'
WHEN -8112270479827337889 THEN 'Cross Cultural Training'
WHEN IN (4054511778913267481, -3963967039857539357) THEN 'Departure'
ELSE 'Unknown'

but i keep on getting error

Incorrect syntax near the keyword 'IN' now can someone let me know if u need to perform an operation like above how can u do it inside a case statement.

Thanks in advance

Cheers,
A...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-29 : 09:26:22

CASE WHEN
sil.revenuecode = 622773258760115409 THEN 'Consultancy'
WHEN sil.revenuecode = -8112270479827337889 THEN 'Cross Cultural Training'
WHEN sil.revenuecode = (4054511778913267481, -3963967039857539357) THEN 'Departure'
ELSE 'Unknown'

Though I strongly advise you to store these values and descriptions in a table and join to it to get your descriptions and not to hard-code data into your SQL statements.

See: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

- Jeff

ankur_gurha
Starting Member

20 Posts

Posted - 2006-08-29 : 09:29:28

Sorry that doesnt work really. It gives me error

Incorrect syntax near '='.

thanks for the help.

Cheers,
A..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-29 : 09:40:49

oops, sorry:

CASE WHEN
sil.revenuecode = 622773258760115409 THEN 'Consultancy'
WHEN sil.revenuecode = -8112270479827337889 THEN 'Cross Cultural Training'
WHEN sil.revenuecode IN (4054511778913267481, -3963967039857539357) THEN 'Departure'
ELSE 'Unknown' END

Be sure to read about the syntax for the CASE expression so that you understand what you are doing and how edit/write others. And most importantly of all, make sure that you read the link I directed you to and be sure to understand how to effectively make use of your database to store data that will make your SQL statements shorter and more flexible and easier to maintain.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources