SQLTeam.com | Weblogs | Forums

Select records with CASE statement


#1

I have a table like this

 Table1
 Items	       Qty
 Item A    	1
 Item B    	5
 Item C    	3
 Item D    	7
 Item E    	1
 Item F    	2
 Item G         10
 Item H    	1
 Item I     	8

I need to write a statement like:

Declare @selection int = (can be 0, 1, or 2)
Select Items, Qty From Table1
Where
CASE
-- when @selection = 0, select all items
-- when @selection = 1, select items that have qty = 1
-- when @selection = 2, select items that have qty > 1
END

How do I implement my CASE statement? Thanks


#2

put a where clause:

where @selection = 0
or @selection = 1 and qty = 1

etc


#3

This code works for me:

Where @selection = 0
OR (@selection = 1 and qty = 1)
OR (@selection = 2 and qty > 1)

Thanks for the advise


#4

You might want to get in the habit of putting parenthesis around the "OR" section so you do not forget if there is an "AND".


#5

... and leaving them off things that are ANDed together, since precedence takes care of it for you


#6

"I'd like a burger and fries or hash-browns"

...

"How come you just got me hash-browns?" :frowning:


#7

cause you gave me a choice?

Note that to my point, this is the same as "I'd like a (bugger [sic] and fries) or hash-browns


#8

Sure, but as a human you knew what I meant, even though it was ambiguous and thus not necessarily conforming to precedence rules :slight_smile:

Well I hope you did ... if you just bring me Hash Browns I'll then realise that you didn't!


#9

Of course, most waiters would confirm the order. If given a choice, they'd simply bring the highest-priced combo. Now what if I asked for "fries and burger or hash browns" ? even more ambiguous. we tend to group things according to categories (fries and hash browns are both potatoes) and good AI can do the same.

Anyway I advocate removing parentheses around ANDed items. Since (burger and fries) means the same as "burger and fries" both to you and SQL (precedence). However, what I suppose you wanted is "burger and (fries or hash browns)" and you certainly do need the parentheses around the ORed items. Both for people and machines!


#10

Ah ... but that would be inversely proportional to their tip, no?!!


#11

no. the bigger the bill, the bigger the tip, right?


#12

Only if I don't spot that I've been shafted ...