SQL Help - CASE - 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

Other Forums

MS Access

SQL Help - CASE

Author

Topic

hotblooded
Starting Member

4 Posts

Posted - 2007-11-30 : 02:45:08

The database I'm using lists tennis players, tournaments, and how the players placed in those tournaments, as well as the prize money awarded for each tournament. I'm trying to create a query that takes the prize money and the place and awards 100% of the money to the first place player, 75% to second and 25% to third. I'm guessing I should use a CASE statement to do this, but I'm not sure. Here's my code:

SELECT P_Name,
(CASE [R_Result]
WHEN 1 THEN Ch_Prize
WHEN 2 THEN Ch_Prize*.75
WHEN 3 THEN Ch_Prize*.25
ELSE 0
END CASE) AS Winnings
FROM CHAMPIONSHIP, RESULT, PLAYER
WHERE CHAMPIONSHIP.Ch_Name=RESULT.R_Championship AND RESULT.R_Name=PLAYER.P_Name;

When I try to view it in Datasheet view, it throws a syntax error saying it's missing an operator. Any ideas on how to fix this problem? I've tried fooling around with it for a while now and can't seem to figure it out...help is greatly appreciated

Thanks in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 02:52:37

You can't use CASE statement in Access. Make use of nested IIF() function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

hotblooded
Starting Member

4 Posts

Posted - 2007-11-30 : 13:14:30

I tried using the nexted IIF statements and it's no longer giving me a syntax error. The answers are just wrong though. Here's what I have:

SELECT Ch_Prize = IIF(R_Result = 1, Ch_Prize,
IIF(R_Result = 2, Ch_Prize*.75,
IIF(R_Result = 3, Ch_Prize*.25,
IIF(R_Result > 3, 0, 0)))) AS Winnings
From CHAMPIONSHIP, RESULT
WHERE CHAMPIONSHIP.Ch_Name = RESULT.R_Championship;

When I switch to datasheet view, it only shows 0s and -1s...any thoughts?

hotblooded
Starting Member

4 Posts

Posted - 2007-11-30 : 13:37:29

I got it to multiply correctly, now my problem is combining the winnings of each player from different tournaments (e.g. Sampras won wimbledon and munich, I need to combine the winnings for him from these two tournaments.) I'm not sure how I would go about doing this.

Any thoughts?

hotblooded
Starting Member

4 Posts

Posted - 2007-11-30 : 19:48:13

GOT IT! I was working on it for 3 hours and someone came in and got it in 5 minutes...he's smart

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2007-12-05 : 12:09:14

Hotblooded,

Can you please post the code you used, I'm trying to work a SQL CASE statement in Access and would like to see what you used.

Thanks

GC

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-08 : 09:02:40

Call me niaive, ight, but if the person who comes first gets 100% of the winnings, then how can 2nd place get 75? :-P

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources