Looking at IF, WHERE, and WHEN in PL SQL

Question

I am completing an academic assignment that asks to prompt the user for their target sales and their employee id. If the target sales exceeds or equates to that of the actual company sales for 2015, then raises can be applied.

I've composed a majority of code but I am stuck on the END IF; statements on line 25. I'm receiving error(s)

ORA-06550, PLS-00103: Encountered the symbol "WHERE" when expecting one of the following.

I think I might be struggling to integrate the if statement that compares the user input to the company sales for 2015.

Insights greatly appreciated! Thank you!

accept emp_target prompt 'Please enter your company sales target: '
accept empno prompt 'Please enter your employee ID: '
DECLARE
emp_target NUMBER := &emp_target; 
cmp_target NUMBER;
empno   emp_employees.emp_id%type := &empno;
new_sal emp_employees.salary%type;
cnt     number; 

CURSOR sales_cur IS 
    SELECT SUM(oe_orderDetails.quoted_price)
    FROM oe_orderDetails
    JOIN oe_orderHeaders
    ON oe_orderDetails.order_id = oe_orderHeaders.order_id
    WHERE oe_orderHeaders.order_date >= to_date('1.1.' || 2015, 'DD.MM.YYYY')
    and oe_orderHeaders.order_date < to_date('1.1.' || (2015 + 1), 'DD.MM.YYYY');

BEGIN
OPEN sales_cur;
FETCH sales_cur INTO cmp_target;
IF cmp_target >= emp_target THEN 
    UPDATE emp_employees SET
        emp_employees.salary = case WHEN emp_employees.dept_id = 10 THEN emp_employees.salary * 1.1
        WHEN emp_employees.emp_id = 145 THEN emp_employees.salary * 1.15
        WHEN emp_employees.dept_id = 80 THEN emp_employees.salary * 1.2
        ELSE emp_employees.salary
    END IF;

END

WHERE emp_employees.emp_id = empno
returning emp_employees.salary into new_sal;

cnt := sql%rowcount;

IF cnt > 0 THEN
    dbms_output.put_line('Employee ' || empno || ', new salary = ' || new_sal);
ELSE
dbms_output.put_line('Nobody got new salary');
END IF;
END;

/

Answer 1

The main issue is that you have misplaced the CASE block's end and where clause out after END IF.

Apart from that I would say that the cursor block was not required to store a simple SUM, but i'll let you use it since you are learning for an assignment.

The other problem after you fix the first one is your returning emp_employees.salary into new_sal. A scalar variable can't contain multiple rows returned from the dml which updates more than one row. You should use a collection(nested table) instead. Use RETURNING BULK COLLECT INTO to load it and loop over later to display your final message.

Please read all my code comments carefully. I cannot test the whole code for any errors as I have none of your tables. You should fix if there are any if you can or let us know if you can't.

ACCEPT emp_target PROMPT 'Please enter your company sales target: '
ACCEPT empno PROMPT 'Please enter your employee ID: '
DECLARE
     emp_target   NUMBER := &emp_target;
     cmp_target   NUMBER;
     empno        emp_employees.emp_id%TYPE := &empno;
     TYPE sal_type IS
          TABLE OF emp_employees.salary%TYPE; --nested table(collection) of salary
     new_sal      sal_type; -- a collection variable
     cnt          NUMBER;
     CURSOR sales_cur IS SELECT SUM(oe_orderdetails.quoted_price)
                         FROM oe_orderdetails
                         JOIN oe_orderheaders ON oe_orderdetails.order_id = oe_orderheaders.order_id
                         WHERE oe_orderheaders.order_date >= TO_DATE('1.1.' || 2015,'DD.MM.YYYY') AND
                         oe_orderheaders.order_date < TO_DATE('1.1.' || (2015 + 1),'DD.MM.YYYY'); --is it required to specify (2015 + 1) instead of 2016?
BEGIN
     OPEN sales_cur;

     FETCH sales_cur INTO cmp_target;

     IF
          cmp_target >= emp_target
     THEN
          UPDATE emp_employees
          SET
               emp_employees.salary =
                    CASE
                         WHEN emp_employees.dept_id = 10 THEN emp_employees.salary * 1.1
                         WHEN emp_employees.emp_id = 145 THEN emp_employees.salary * 1.15
                         WHEN emp_employees.dept_id = 80 THEN emp_employees.salary * 1.2
                         ELSE emp_employees.salary
                    END
          WHERE emp_employees.emp_id = empno --misplaced where and end
           RETURNING emp_employees.salary BULK COLLECT INTO new_sal;
     END IF;

     cnt := new_sal.count; --no of records in nested table

     IF
          cnt > 0
     THEN
          FOR i IN new_sal.first..new_sal.last LOOP
               dbms_output.put_line('Employee ' || empno || ', new salary = ' || new_sal(i) );
          END LOOP;
     ELSE
          dbms_output.put_line('Nobody got new salary');
     END IF;
     CLOSE sales_cur; -- always remember to close the cursor
END;

/