June 5, 2009 at 6:13 am
I want to Toggle between 'a row alone' and 'all rows except that row'
The idea is (in an example):
DECLARE @Show4 VARCHAR(1) SET @Show4 = 'y'
SELECT * FROM
( -- Test Data base
select ID = 1 , aName = 'ID1' union all
select ID = 2 , aName = 'ID2' union all
select ID = 3 , aName = 'ID3' union all
select ID = 4 , aName = 'ID4' union all
select ID = 5 , aName = 'ID5'
) a
WHERE CASE WHEN @Show4 = 'y' THEN ID = 4 ELSE ID <> 4 END
resulting
in (@Show4 = 'y'):
4 ID4
or (@Show4 = 'n'):
1 ID1
2 ID2
3 ID3
5 ID5
The WHERE clause gives an error. Anyone who knows the correct WHERE code?
June 5, 2009 at 6:17 am
EDIT -- Sorry was thinking of the wrong thing.
you might want to create a dynamic query for this type of problem
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 5, 2009 at 6:41 am
The correct construct you were looking for is
WHERE CASE WHEN @Show4 = 'y' AND a.[ID] = 4 THEN 1 WHEN @Show4 = 'n' AND ID 4 THEN 1 ELSE 0 END = 1
However performance will be poor and you should look for an alternative as mentioned in Chris's reply.
Far away is close at hand in the images of elsewhere.
Anon.
June 5, 2009 at 6:43 am
I tried:
WHERE (ID = 4 AND @Show4 = 'y')
--OR
--AND
--(ID 4 AND @Show4 'y')
but OR or AND didn't do the trick 🙁
June 5, 2009 at 6:49 am
but OR or AND didn't do the trick
Why didn't they do the trick, this should work
WHERE (@Show4 = 'y' AND ID = 4) or (@show4 'y' AND ID 4)
If that gives a poor query plan, then revert to good old-fashioned:
IF @show = 'y'
SELECT....
WHERE ID = 4
ELSE
SELECT....
WHERE ID 4
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply