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.