[RESOLVED] SQL Case Problem

I am trying to use Case to catch reward totals that are greater then 100 so that I can set the max at 100. I am getting on the When Total > 100 Line. It says

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '>'.

Code:

---

SELECT        DISTINCT ACCOUNTID       
        , Bonus
        , ColA
        , ColB
        , ColC
        , Reward = CASE Total                                               
                WHEN Total > 100 THEN 100
                ELSE Total
                END AS
FROM    Rewards

---

Ouch, you've made a couple of mistakes there... first of all, to use an alias you don't use = , you just put "as YourAlias" at the end.

Next up, your Case looks a bit odd.. but how it should be depends entirely on what database system you are using, and you didn't tell us.

Here is an educated guess of the syntax you'll need:

Code:

---

        , CASE Total                                               
            WHEN > 100 THEN 100
            ELSE Total
          END
          AS Reward

---

Thank you SI. I started with someting similar to that but I couldn't get it to work so I ended up with the SQL I posted above. I adjusted it per your suggestion and am still getting the same error. It is SQL Server 2005.

Code:

---

SELECT        DISTINCT AccountID
              , Bonus
        , ColA
        , ColB
        , ColC
        , CASE Total                                               
                WHEN > 100 THEN 100
                ELSE Total
                END
                AS Reward
FROM        AWARDSSUMMARY

---

OK, well here's the two variations for SQL Server 2005:

Code:

---

Simple CASE function:
CASE input_expression
    WHEN when_expression THEN result_expression
    [ ...n ]
    [
    ELSE else_result_expression
    ]
END
Searched CASE function:
CASE
    WHEN Boolean_expression THEN result_expression
    [ ...n ]
    [
    ELSE else_result_expression
    ]
END

---

The first version looks like what you want, except that when_expression needs to be a value (of the same type as the input_) rather than a condition.. which means you need to use the second, so like this:

Code:

---

        , CASE
            WHEN Total > 100 THEN 100
            ELSE Total
          END
          AS Reward

---

That worked. Thank you.