CASE in a WHERE clause?

How can I do this?

Select somedata

From sometable

Where somecolumn = somevalue

and

CASE WHEN someparam = 1 THEN somecolumn2 <> somevalue2 ELSE somecolumn2 like '%'

Or this?

Select somedata

From sometable

Where somecolumn = somevalue

IF someparam = 1 AND somecolumn2 <> somevalue2

The error I am getting is Incorrect syntax near '<' using the first statement. Using the second statement I get an 'Error near IF' error or something like that.

Thanks for any help!

Hi !

As you see you can't do it that way, but you probably could use derived table syntax:

select dT.*, case when dt.newcol1>42 then '42t' else cast(dt.newcol2 as varchar(10)) end newcolX

from

( select t.*, case when t.col1='a' then 'A' else '' end newcol1,

case t.col2 when 2 then col3+col4 else col5 end newcol2

-- you can also use nested case...

from sometable t

) dt

where dt.newcol2 like 'text';

Best regards

Bjorn

P.S. there is no meaning in select-stmt, I'm just showing syntax...

|||

1.

Not sure this is the best way but you could generate your sql dynamically as a varchar then use EXEC to run the varchar

2.

Again, not sure this is correct but try:

.....WHERE (somecolumn = somevalue AND someparam = 1 AND somecolumn2 <> somevalue2)

OR

someparam <> 1

Perhaps even a union query could work for number 2

like I said they probably aren't 100% correct but hopefully point you in the right direction

|||hi,

i'm not sure what your requirements really are.. hope you can gets something from this example

declare @.ab varchar(100)

set @.ab = 1

select *
from (
select 'the quick' as a, 'brown fox' as b union all
select 'jumps over' as a, 'tha lazy dog' as b
) ab
where b = 'brown fox'
and a <> (case when @.ab = 1 then 'the quick' else '%' end)

set @.ab = 2

select *
from (
select 'the quick' as a, 'brown fox' as b union all
select 'jumps over' as a, 'tha lazy dog' as b
) ab
where b = 'brown fox'
and a <> (case when @.ab = 1 then 'the quick' else '%' end)

again, it really depends on the conditions that you need to evaluate|||

Use the following query..

Code Snippet

Select

somedata

From

sometable

Where

somecolumn = somevalue

And

(

(someparam = 1 And somecolumn2 <> somevalue2)

Or

(someparam <> 1 And somecolumn2 like '%')

)

|||

Thank you!!

|||

sarolabelle,

A "case" expression returns a value, so you need to compare the value returned by the "case" expression to something, in order to have a valid logic expression.

Select

somedata

From

sometable

Where

somecolumn = somevalue

and

CASE

WHEN someparam = 1 and somecolumn2 <> somevalue2 then 1

WHEN someparam != 1 and somecolumn2 like '%' then 1

else 0

end = 1

go

AMB

|||

Try this one

-- Case in Where

Code Snippet

Select * From EMP

Where (Country = 'India')

AND

(CASE

WHEN Salary < 5000 then 1

WHEN Age > 18 then 1

END = 1)

-- Case & where (individual)

Code Snippet

SELECT Name, Salary,

CASE

WHEN Salary < 5000 THEN 'No Tax'

WHEN Salary > 5000 THEN 'High Tax'

WHEN Salary = 5000 THEN 'Low Tax'

ELSE 'Invalid Data'

END

AS Tax

FROM EMP

WHERE country = 'USA'

for more details logon www.sqltree.com (under Conditional Processing folder)

Thanks

|||SQLTree -> It is not answering the question. Here the requirement is filtering the data.