CODEWITHSUNDEEP
sqlcase
Ilian Grekov
Ilian Grekov

Reputation: 51

SQL CASE WHEN bigger than

I've been searching a lot and in the examples everything looks fine, but in fact it doesn't. I want to calculate the salary increase dependently on the amount. Missing keyword error appears.

SELECT o.operator_name,
       o.salary,
(CASE o.salary
 WHEN o.salary < 500 THEN o.salary * 1.35
 WHEN o.salary >= 500 AND o.salary < 1000 THEN o.salary * 1.15
 WHEN o.salary >= 1000 AND o.salary < 1500 THEN o.salary * 1.05 
 END) AS "Increase"
FROM operators o;

I tried with IF ELSIF, I got "FROM is not where is expected".

Upvotes: 1

Views: 10242

Answers (4)

Hart CO
Hart CO

Reputation: 34784

You've mixed two formats of CASE, try:

SELECT o.operator_name,
       o.salary,
       CASE WHEN o.salary < 500 THEN o.salary * 1.35
            WHEN o.salary >= 500 AND o.salary < 1000 THEN o.salary * 1.15
            WHEN o.salary >= 1000 AND o.salary < 1500 THEN o.salary * 1.05 
            ELSE o.salary
       END AS "Increase"
FROM operators o;

This format allows for multiple/disparate fields to be used as well as comparison operators. You may also be needing an ELSE before the END if you want something to be returned for salary values 1500 and over.

It's also worth noting that CASE exits at the first met criteria, so you could use:

SELECT o.operator_name,
       o.salary,
       CASE WHEN o.salary < 500 THEN o.salary * 1.35
            WHEN o.salary < 1000 THEN o.salary * 1.15
            WHEN o.salary < 1500 THEN o.salary * 1.05 
            ELSE o.salary
       END AS "Increase"
FROM operators o;

The other format of CASE wouldn't work for your example, but here's a simple example to show usage:

SELECT CASE col1 
           WHEN 'cat' THEN 1  
           WHEN 'dog' THEN 2 
       END
FROM table1

That is equivalent to:

SELECT CASE WHEN col1 = 'cat' THEN 1 
            WHEN col1 = 'dog' THEN 2 
       END
FROM table1

Upvotes: 7

John Pink
John Pink

Reputation: 637

It looks like you're missing the default case. You have to finish your case with an 'ELSE default_value'. So :

CASE foo
  WHEN bar THEN 1
  WHEN baz THEN 2
  ELSE 0
END

Upvotes: 0

Drew
Drew

Reputation: 2663

SELECT o.operator_name, 
       o.salary, 
       CASE 
         WHEN o.salary < 500 THEN o.salary * 1.35 
         WHEN o.salary >= 500 
              AND o.salary < 1000 THEN o.salary * 1.15 
         WHEN o.salary >= 1000 
              AND o.salary < 1500 THEN o.salary * 1.05 
       END AS "Increase" 
FROM   operators o;

Should do the trick!

Upvotes: 0

Kyle Hale
Kyle Hale

Reputation: 8120

Looks like you want ELSE:

SELECT o.operator_name,
       o.salary,
(CASE 
 WHEN o.salary < 500 THEN o.salary * 1.35
 WHEN o.salary >= 500 AND o.salary < 1000 THEN o.salary * 1.15
 WHEN o.salary >= 1000 AND o.salary < 1500 THEN o.salary * 1.05 
ELSE o.salary
 END) AS "Increase"
FROM operators o;

Upvotes: 2

Related Questions