case statement in where clause
-
How do you write a case statement like this for the <> 2 part?
select field1
from tbl1
where field2 =
case when field3 = 1 then 1
when field 3 = 2 then <> 2
end
-
Hi Regina,
Would you be able to elaborate a bit more on the relationship between field2 and field3? We need it in order to workout the CASE you are trying to implement in your query.
Cheers,
Hope this helps,
Rock from VbCity -
I'm sorry. It was a bad and confusing example. If field2 contains values 1-10 and some of them are null, I only want to display the values that aren't null except the value 2 when field 3 = 'b'
select field1
from tbl1
where field2 =
case
when field3 = 'a' then 1
when field 3 = 'b' then <> 2
end
-
You can't do that the way you are doing it because it clashes with the "=" operator you used for Field2 in your Where Clause. But, you can do it separately like this:
Select Field1 From Ex
Where
Field2 = (Case When Field3 = 'a' Then 1 Else '' End) And
Field2 <> (Case When Field3 = 'b' Then 2 Else '' End)
If this doesn't help then please read the link in my signature and post your requirement as mentioned in the link. Some explanation on your Business Requirement would also be good and would get you a solution very quickly.
-
I did not find an easy way to implement a filter (WHERE) using a CASE clause on the field2 and field3; the query below does the work
SELECTfield1
FROM
tbl1
WHERE
field2 IS NOT NULL -- the values that aren't null
AND NOT (field3 = 'b' AND field2 = 2 ) -- except the value 2 when field 3 = 'b'
Hope this helps,
Rock from VbCity -
The example given by vinu512 did not work for me, the query below with CASE does work using the sample data I used. I do not like because become to tricky.
SELECTfield1
FROM
tbl1
WHERE
Field2 = (CASE
WHEN Field3 = 'a' THEN Field2
ELSE
CASE
WHEN Field3 = 'b' THEN ABS(SIGN(2-Field2)) * Field2
ELSE Field2
END
END
Hope this helps,
Rock from VbCity -
Rock from VbCity (6/4/2012)
The example given by vinu512 did not work for me, the query below with CASE does work using the sample data I used. I do not like because become to tricky.It wasn't much of an example. I was just telling the OP the concept.
The Expected Result can be achieved using Case like this:
Select Field1 From Ex
Where
Field2 = (Case When Field3 = 'a' Then Field2
When Field3 = 'b' Then
Case When Field2 = 2 Then 0 Else Field2 End
Else ''
End)
-
I agree, Regina did not provide sample data
Hope this helps,
Rock from VbCity -
I think when Field3 = 'a' you want to equate Field2 = 1 and Field3 = 'b' equate Field2 <> 2
This should work,
Where(Field3 = 'a' and Field2 = 1)
OR
(Field3 = 'b' and Field2 <>2)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply