CASE in WHERE-statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CASE in WHERE-statement

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-16 : 08:31:30

Can something like this be done:

SELECT * FROM table
WHERE CASE 
    WHEN Request = 1 THEN Price <= @Price
    ELSE Price >= @Price
  END

This might be confusing but what I need is all prices less than or equal to @Price if Request = 1 and all prices greater than or equal to @Price if Request = 0. Anyone?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-16 : 08:45:26

logically:

if A then B else C

is equivalent to:

(A and B) OR ((not A) and C)

so your where clause would look like this:

WHERE (Request =1 AND Price <= @Price) OR (Request=0 AND Price >= @Price)

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-08-16 : 08:47:35

Hi Lumbago but can you not just do this :


SELECT * FROM table
WHERE Request = 1
and Price <= @Price
union all
SELECT * FROM table
WHERE Request = 0
and >= @Price

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-16 : 14:16:21

Well, I appreciate the suggestions but I'm really unsure if I can use any of them. I'm actually using this in a subselect within a case and to be honest I'm not 100% sure how to use your suggestion. Here is the actual query:

SELECT MarketID, CompID, Request, Price, Volume, Rank
  (SELECT MarketID, CompID, Request, Price, Volume = SUM(Volume_euro), 
    Rank = CASE 
      WHEN Request = 1 THEN 
        (SELECT COUNT (DISTINCT Price) FROM table
        WHERE MarketID = a.MarketID AND CompID = a.CompID AND Request = 1
        AND Price <= a.Price AND Volume > dbo.MinStake ('EUR')) 
      ELSE
        (SELECT COUNT (DISTINCT Price) FROM table
        WHERE MarketID = a.MarketID AND CompID = a.CompID AND Request = 0
        AND Price >= a.Price AND Volume > dbo.MinStake ('EUR')) 
      END
  FROM table a WITH (NOLOCK)
  WHERE MarketID = @MarketID 
  GROUP BY MarketID, CompID, Request, Price
  HAVING SUM(Volume_euro) > 0
  ) 
WHERE Rank <= 3

What I would like is to have the same funtionality in an easier way...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-08-16 : 14:43:51

WHERE (Request*2-1) * Price <= @Price

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-16 : 17:05:50

Now THAT fellow yaks, is pure genious!! I have been looking at it for 10 minutes now and finally I understand...tears are almost coming from my eyes...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-16 : 19:15:36

Arnold, you worry me. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-17 : 02:24:23

Fantastic solution Arnold :)

The only thing is - I think that most other people reading the code will battle to understand what's happening there (Unless it is well documented).

Duane.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-17 : 04:29:46

After testing a little I had to do one minor modification, but still this is just soooooo beutiful:

WHERE (Request*2-1) * Price <= (Request*2-1) * @Price

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-17 : 18:47:28

A bit clearer is
where ((request = 0 and Price >= @Price) or (request = 1 and Price <= @Price))

also
where sign(@Price - Price) * (Request - 0.5) >= 0

where abs(convert(int,sign(@Price - Price)) ^ (2 * Request - 1)) < 2
(just wanted to use a bit operator)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources