CODEWITHSUNDEEP
oracle-databaseplsqlcase
Roy
Roy

Reputation: 73

"ORA-00905: missing keyword" for case statement

DECLARE
var_employee_number VARCHAR2(30);
var_employee_name VARCHAR2(30);
var_salary VARCHAR2(30);
BEGIN
SELECT employee_number,employee_name,
CASE var_salary
    WHEN salary <= 10000 THEN salary:= salary* .02 /*I am getting an error over here*/
    WHEN salary BETWEEN 10000 AND 15000 THEN salary := salary * .02
    WHEN salary BETWEEN 15000 AND 20000 THEN salary := salary * .025
    END AS salary INTO var_employee_number,var_employee_name,var_salary
FROM EMPLOYEES
WHERE employee_number := var_employee_number;
dbms_output.put_line('The salary is '||var_salary);
END;

I am trying to write a case statement when if your salary is in a particular range, the salary should be multiplied by either 20% or 25% depending on the salary range.

Upvotes: 0

Views: 4293

Answers (1)

David Faber
David Faber

Reputation: 12485

Your CASE statement has a couple of problems:

CASE var_salary -- you don't need var_salary here
    WHEN salary <= 10000 THEN salary:= salary* .02 /*I am getting an error over here*/ -- you don't need "salary:=" here
    WHEN salary BETWEEN 10000 AND 15000 THEN salary := salary * .02
    WHEN salary BETWEEN 15000 AND 20000 THEN salary := salary * .025
END AS salary

So correcting for these issues it should read just:

CASE WHEN salary <= 10000 THEN salary * 0.2
  WHEN salary BETWEEN 10000 AND 15000 THEN salary * 0.2
  WHEN SALARY BETWEEN 15000 AND 20000 THEN salary * 0.25
END AS salary

You might also note that the cases for <= 10000 and BETWEEN 10000 AND 15000 are the same, and so could be combined.

Also keep in mind that BETWEEN ... AND is inclusive, so a value of 15000 will return 15000 * 0.2 (3000), rather than * 0.25.

Also, please note that I corrected your numbers ... 20% is 0.2, not .02.

Upvotes: 3

Related Questions