asked
use case in where clause
Dear All I want to merge the below statements in one querry select * from table1 where col1=@col select * from table1 where col2=@col I want to use third variable and use case statement to handle both conditions
Not quite sure what you're driving at here. Could you explain in a bit more detail?
Presumably the 3rd variable says whether to compare with col1 or col2. So you can do it like this: select * from table1 where (@colNo = 1 and col1 = @col) or (@colNo = 2 and col2 = @col);
OK. In that case I think it should be: select * from table1 where (cond1 = @cond1 and col1 = @col1) or (cond1 @cond1 and col2 = @col2);
Shouldn't that be... where (cond1 = @cond1 and col1 = @col1) or (con1 @cond1 and col2 = @col2) After all, there may be cases where the col2 match applies if the cond1 also applies, and that might not be what the OP requires... just a thought
Well done for spotting my deliberate mistake ;) Seriously though, yes you're right. I've corrected it in case anyone in the future doesn't read the whole trail.
the problem is still there any solution??
You didn't mention a problem before. You're going to have to explain better if you want an answer.
i want to switch between two conditions in the where clause keeping select statement the same like select * from table1 where col1=@col1 and select * from table1 where col2=@col2 i want to merge it in one query 2nd solution of this problem is if @cond=1 select * from table1 where col1=@col1 else select * from table1 where col2=@col2 but i want this to achieve using case statement in where clause
Right. This should do the trick: select * from table1 where (@cond=1 and col1=@col1) or (@cond1 and col2=@col2); I don't know why you keep insisting on a CASE statement. You might be able to use one in a WHERE clause. I don't know. But this should work fine.
21 People are following this question.
