case statement in where clause

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

case statement in where clause

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-04-30 : 01:55:45


select company_code from fin_ods.dbo.company_fb_mst as mst2 with (nolock) 
where   
		 case when @Company_code_in='Asean'  then geography1 in('singapore','malaysia')
			  when @Company_code_in!='Asean' then geography1 like @Company_code_in
		 end

if the input company is "asean" then i need to use "IN " condtn in where clause for a column "Geography" else if company is not as "Asean" then i need to use "like " condtn for a column "Geography"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-30 : 02:16:25

[code]select company_code
from fin_ods.dbo.company_fb_mst as mst2 with (nolock)
where case
when @Company_code_in = 'Asean' and geography1 in ('singapore','malaysia') then 1
when @Company_code_in <> 'Asean' and geography1 like @Company_code_in then 1
else 0
end = 1[/code]

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

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-30 : 02:21:20

Try it,

select company_code from fin_ods.dbo.company_fb_mst as mst2 with (nolock)
where charindex(geography1 + ',' , case when @Company_code_in='Asean' then 'singapore,malaysia,'
else @Company_code_in +',' end) > 0

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-04-30 : 02:43:37

txs peso,its working .

quote:

Originally posted by Peso

select 	company_code
from 	fin_ods.dbo.company_fb_mst as mst2 with (nolock)
where   case
		when @Company_code_in = 'Asean' and geography1 in ('singapore','malaysia') then 1
		when @Company_code_in <> 'Asean' and geography1 like @Company_code_in then 1
		else 0
	end = 1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 03:17:49

See if this works too


select 	company_code
from 	fin_ods.dbo.company_fb_mst as mst2 with (nolock)
where   (@Company_code_in = 'Asean' and geography1 in ('singapore','malaysia'))
		or 
		(@Company_code_in <> 'Asean' and geography1 like @Company_code_in)

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 11:56:05

Alternativly:

select 	company_code
from 	fin_ods.dbo.company_fb_mst as mst2 with (nolock)
where   @Company_code_in = 'Asean' 
	and 
	(
		geography1 in ('singapore','malaysia')
		or geography1 like @Company_code_in)
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources