Sql Case Expression Statement

Question

0.00/5 (No votes)

See more:

id mark1 mark2 mark3
1 85 85 89
2 78 78 98
3 67 67 75
4 79 79 76
5 76 76 83

I have the above table called marks. I've written the following query.

SQL

SELECT ID, CASE MARK1
WHEN mark1 > 85 THEN (MARK1*2)
ELSE MARK1
END
"NEW MARK1"
FROM marks

If mark 1 > 70 i want to multiply tat data by 2. but when i run tat query i m getting the following error.

Incorrect syntax near '>'. what's wrong with tat query?

Posted 12-Aug-13 1:20am

ARUN K P

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Adarsh chauhan · Accepted Answer · 2013-08-12T01:22:00

Solution 1

Use it like this..

SQL

SELECT ID, CASE WHEN mark1 > 85 THEN (MARK1 * 2)
ELSE MARK1 END [NEW MARK1]
FROM marks

Posted 12-Aug-13 1:22am

Adarsh chauhan

Updated 12-Aug-13 1:23am

v2

Comments

ARUN K P 12-Aug-13 7:23am

Thanks adarsh.
+5

Adarsh chauhan 12-Aug-13 7:25am

well you forgot to click +5 :)

ARUN K P 12-Aug-13 7:26am

how to click +5. ??
i m just typing +5 and entering.

Stephen Hewison 12-Aug-13 7:28am

Click the stars on the top right of the solution

ARUN K P 12-Aug-13 7:30am

Ok done stephen thanks :)

Stephen Hewison · Accepted Answer · 2013-08-12T01:23:00

Solution 2

You're using the case statement in two ways at the same time

If testing for a specific value you can:

SQL

CASE MARK1 WHEN 10 THEN doThis ELSE doThat END

Or you can have multiple types of test like this:

SQL

CASE WHEN MARK1 > 10 THEN doThis ELSE doThat END

What you've written is a bit of a mash up between the two.

Posted 12-Aug-13 1:23am

Stephen Hewison

Comments

ARUN K P 12-Aug-13 7:24am

ok thanks stephen

Adarsh chauhan 12-Aug-13 7:24am

nice explanation... +5