CASE statement syntax help

If someone predicts the correct score in a match, they get 3 points. If
they predict the correct result, but not the correct score, they get 1
point. Otherwsie they get none. As part of the sp that adds the score to a
Fixture, I also want to update the points relating to the predictions for
that fixture. I pass in 1 for the FixtureID (which exists in the
Predictions table), 2 and 1 for the Home team and Away team goals
respectively, but the following affects 0 rows:

UPDATE Predictions SET Points =
CASE
WHEN HomeTeamGoals = @HomeTeamScore AND AwayTeamGoals = @AwayTeamScore
THEN 3
WHEN ((HomeTeamGoals > AwayTeamGoals AND @HomeTeamScore > @AwayTeamScore)
OR
(HomeTeamGoals = AwayTeamGoals AND @HomeTeamScore = @AwayTeamScore) OR
(HomeTeamGoals < AwayTeamGoals AND @HomeTeamScore < @AwayTeamScore))
THEN 1
ELSE 0
END
WHERE Predictions.FixtureID = @FixtureID

What have I done wrong?

I don't see anything obvious that is incorrect, but it could be data
related. But, you should have a FROM clause in your update
statement.

UPDATE
p
SET
p.Points = CASE WHEN COALESCE(p.HomeTeamGoals, 0) = @HomeTeamScore
AND COALESCE(p.AwayTeamGoals, 0) =
@AwayTeamScore THEN 3
WHEN ((COALESCE(p.HomeTeamGoals, 0) >
COALESCE(p.AwayTeamGoals, 0)
AND @HomeTeamScore > @AwayTeamScore)
OR (COALESCE(p.HomeTeamGoals, 0) =
COALESCE(p.AwayTeamGoals, 0)
AND @HomeTeamScore = @AwayTeamScore)
OR (COALESCE(p.HomeTeamGoals, 0) <
COALESCE(p.AwayTeamGoals, 0)
AND @HomeTeamScore < @AwayTeamScore))
THEN 1
ELSE 0
END
FROM
Predictions p
WHERE
p.FixtureID = @FixtureID

We need the DDL for the predictions table and the declare statements
for your variables to help you find the problem. Also document some
of the data in the table.

-Eric Isaacs

Oh, I'm such a numpty. I was passing in a non-existent FixtureID value.
Well, it exists, but not in the Predictions table... I noticed that after I
replaced my original code with yours, which works. I'm off to research
COALESCE now. Thank you.

Mike



"Eric Isaacs" wrote in message
news:ccff858a-8174-4746-87e6-fa5459333e0f-at-s20g2000prd.googlegroups.com...
>I don't see anything obvious that is incorrect, but it could be data
> related. But, you should have a FROM clause in your update
> statement.
>
> UPDATE
> p
> SET
> p.Points = CASE WHEN COALESCE(p.HomeTeamGoals, 0) = @HomeTeamScore
> AND COALESCE(p.AwayTeamGoals, 0) =
> @AwayTeamScore THEN 3
> WHEN ((COALESCE(p.HomeTeamGoals, 0) >
> COALESCE(p.AwayTeamGoals, 0)
> AND @HomeTeamScore > @AwayTeamScore)
> OR (COALESCE(p.HomeTeamGoals, 0) =
> COALESCE(p.AwayTeamGoals, 0)
> AND @HomeTeamScore = @AwayTeamScore)
> OR (COALESCE(p.HomeTeamGoals, 0) <
> COALESCE(p.AwayTeamGoals, 0)
> AND @HomeTeamScore < @AwayTeamScore))
> THEN 1
> ELSE 0
> END
> FROM
> Predictions p
> WHERE
> p.FixtureID = @FixtureID
>
> We need the DDL for the predictions table and the declare statements
> for your variables to help you find the problem. Also document some
> of the data in the table.
>
> -Eric Isaacs

Eric,

The use of FROM in this UPDATE statement is not recommended (by me, anyway).
UPDATE .. FROM is Microsoft T-SQL proprietary syntax, and in this case, the
FROM clause has no purpose (though it doesn't break the query).

Steve Kass
Drew University
http://www.stevekass.com

Eric Isaacs wrote:

>I don't see anything obvious that is incorrect, but it could be data
>related. But, you should have a FROM clause in your update
>statement.
>
>UPDATE
> p
>SET
> p.Points = CASE WHEN COALESCE(p.HomeTeamGoals, 0) = @HomeTeamScore
> AND COALESCE(p.AwayTeamGoals, 0) =
>@AwayTeamScore THEN 3
> WHEN ((COALESCE(p.HomeTeamGoals, 0) >
>COALESCE(p.AwayTeamGoals, 0)
> AND @HomeTeamScore > @AwayTeamScore)
> OR (COALESCE(p.HomeTeamGoals, 0) =
>COALESCE(p.AwayTeamGoals, 0)
> AND @HomeTeamScore = @AwayTeamScore)
> OR (COALESCE(p.HomeTeamGoals, 0) <
>COALESCE(p.AwayTeamGoals, 0)
> AND @HomeTeamScore < @AwayTeamScore))
>THEN 1
> ELSE 0
> END
>FROM
> Predictions p
>WHERE
> p.FixtureID = @FixtureID
>
>We need the DDL for the predictions table and the declare statements
>for your variables to help you find the problem. Also document some
>of the data in the table.
>
>-Eric Isaacs
>
>

> replaced my original code with yours, which works. *I'm off to research
> COALESCE now. Thank you.

You probably don't need the COALESCE, but since I couldn't see the
data in your table, I couldn't see if there were NULL values
involved. COALESCE just allows you to specify a different value(s) if
the first value(s) are NULL. You could also change COALESCE to ISNULL
in your case, since there are only 2 parameters involved, but ISNULL
is proprietary, and COALESCE is an ANSI standard.

-Eric Isaacs

Yes, I agree, the FROM clause isn't necessary in this case. FROM can
be very helpful in other cases, but here it only helps with allowing
aliasing fo the updated table. Thanks Steve.

-Eric Isaacs

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

I can guess at what the Predictions table might look like, but where
is the Actual outcomes table? What you seem to be doing is
fundamentally wrong -- two different kinds of data in one table!!
Look up one of my favorite phrases - "Automobiles, Squids and Britney
Spears" for the conceptual error you have here. What you posted would
let me maker my predictions AFTER the game, since you have no temporal
elements!!

After we get a Normalized schema, we can write a VIEW that will look
at predictions, join them to outcomes and give each predictor a
score. Want to try again with something that works?

Thanks ever so much for your contribution. I have spent some considerable
time trying to find a whiff of a hint of a germ of anything useful in it
that helps to resolve my original question, and failed. Fortunately, Eric
helped me see the error of my ways a few days ago.

If, in any furture questions I might ask here, my code doesn't follow your
preferred standards, I apologise, but please feel free to totally ignore my
posts if that that helps you.


"--CELKO--" wrote in message
news:efc79962-e0a8-4f47-b3ad-0a37499cd3da-at-d1g2000hsg.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. If you know how, follow ISO-11179 data element naming
> conventions and formatting rules. Temporal data should use ISO-8601
> formats. Code should be in Standard SQL as much as possible and not
> local dialect.
>
> Sample data is also a good idea, along with clear specifications. It
> is very hard to debug code when you do not let us see it. If you want
> to learn how to ask a question on a Newsgroup, look at:
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> I can guess at what the Predictions table might look like, but where
> is the Actual outcomes table? What you seem to be doing is
> fundamentally wrong -- two different kinds of data in one table!!
> Look up one of my favorite phrases - "Automobiles, Squids and Britney
> Spears" for the conceptual error you have here. What you posted would
> let me maker my predictions AFTER the game, since you have no temporal
> elements!!
>
> After we get a Normalized schema, we can write a VIEW that will look
> at predictions, join them to outcomes and give each predictor a
> score. Want to try again with something that works?

>> Thanks ever so much for your contribution. I have spent some considerable time trying to find a whiff of a hint of a germ of anything useful in it that helps to resolve my original question, and failed. <<

All I asked was basic netiquette and pointed out that your design as
described by narrative rather than DDL, has fundamental flaws. And
then, by your own admission, you posted code with errors in it!!

>> Fortunately, Eric helped me see the error of my ways a few days ago. <<

As Steve pointed out, what you got was dangerous proprietary Kludge.

>> If, in any future questions I might ask here, my code doesn't follow your preferred standards, I apologize, but please feel free to totally ignore my posts if that that helps you. <<

What I tried to teach you was *basic netiquette* and ISO standards.
Also, a little about proper data modeling. Those things *are* my
preferred standards, but they are also the preferred standards of this
industry. If you would like to work in it, you might want to learn
about them.

You might want to consider that I helped write the Standards for SQL
and you are the guy who had to look up COALESCE(). Or you can keep
trolling newsgroups for kludges instead of using them to learn your
trade.

> What I tried to teach you was *basic netiquette* and ISO standards.

Since you have apparantly decided to take on the role of keeper of
basic netiquette, can we assume that from now on you will:

- Treat people with some respect instead of being arrogant and rude
to everyone.

- Actually post responses that are relevant to what they're asking -
ex. when someone asks about how to do a join, you won't go into a
three paragraph rant on how bad their table and column names are.

- Actually try your code before hitting send to ensure that it runs,
and that it runs on SQL Server.

- Stop selectively adhering to standards.

- Stop posting advice that has been clearly demonstrated as horribly
inefficient - ex. the "thousands of parameters" argument.

- Stop telling people that they're going to be thrown in jail for
violating various laws despite not actually knowing if their databases
are subject to those laws.

- Stop shamelessly pimping your books at every opportunity.

I for one look forward to seeing the "new" Celko.