Using Case in Where Statement - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Using Case in Where Statement

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-12-06 : 12:11:35

Can someone please help me modify the below query?
I am trying to do the following: When division = 'Both' use revenue in the where clause, otherwise just use division.

select revenue, division from Test
WHERE division = 'Both'

Thanks
Q

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:16:23

[code]
select revenue, division from Test
WHERE ((division <> 'Both' and division = @somevalue) or revenue = @somevalue))
[/code]

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

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-12-06 : 12:23:44

Thanks Visa, I thought a case statement would have been used in the where clause. I am missing how this works. Not sure what @somevalue is....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:26:23

its value you pass through parameter

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

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-12-06 : 13:55:24

Hi Visa, I know it's not what you have suggested, but could you please tell me why this sql statement is not valid?

select * into #test from dbo.ABC
case when @revCoParam like '%aaa%' or @revCoParam like '%bbb%'
then where Division like + '%' + @revCoParam + '%'
else
where Revenue_Company like + '%' + @revCoParam + '%'
End
order by ccc

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-06 : 14:11:56

It's hard to know what you are try to do as you are mixing expressions and predicates which makes no sense to me. CASE is an expression or function, it evaluates to a value. A predicate, or WHERE clause, is used to restrict the result set by way of boolean expressions. So you would compare the result of the CASE expression to some other value, thereby creating a predicate. So, it, might, look like:

WHERE
    Something = CASE ... END

nerdygirl61
Starting Member

21 Posts

Posted - 2011-12-06 : 20:29:00

The question is kind of vague, but I was thinking you are trying to do this

SELECT (CASE
WHEN division = 'Both'
THEN revenue
ELSE division
END) AS FieldName
FROM Test

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:26:44

quote:
Originally posted by qman

Hi Visa, I know it's not what you have suggested, but could you please tell me why this sql statement is not valid?

select * into #test from dbo.ABC
case when @revCoParam like '%aaa%' or @revCoParam like '%bbb%'
then where Division like + '%' + @revCoParam + '%'
else
where Revenue_Company like + '%' + @revCoParam + '%'
End
order by ccc

as per your logic above, it should be


select * into #test from dbo.ABC
where ((@revCoParam like '%aaa%' or @revCoParam like '%bbb%')
and Division like + '%' + @revCoParam + '%')
or Revenue_Company like + '%' + @revCoParam + '%'

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

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-12-07 : 07:58:14

Visa, I owe you a case of beer for all the help you have given me over the past few months! You the man!

Thanks
Q

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 08:33:27

quote:
Originally posted by qman

Visa, I owe you a case of beer for all the help you have given me over the past few months! You the man!

Thanks
Q

Welcome

I will let you know when I come to your part of the world for having the beer

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources