[RESOLVED] SQL Case Problem

[FastEddie]

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 '>'.

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

[si_the_geek]

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:

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

[FastEddie]

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.

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

[si_the_geek]

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

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:

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

[FastEddie]

That worked. Thank you.