IF..THEN...ELSE logic in nested FOR loops

[Guinea]

I have nested FOR loops to retreive two fields from a record. I then have IF..THEN..ELSEIF logic that compares one of the fields and uses the other field for a calculation. When I try to run this I keep getting errors:

ORA-06550 Encountered the symbol "ITEM_TOT_RECORD" when expecting one of the.....
ELSEIF item_tot_record.commplan = 'B'THEN

I cannot figure out if I have a syntax error

Can use IF THEN ELSEIF logic in a nested FOR Looop right?

Can I use a field retreived by the cursor in the boolean comparison?

Thanks for your help!
-Marc

I have pasted the code below.






SET SERVEROUTPUT ON;
ACCEPT p_emp_id PROMPT 'What is the employee ID: ';
ACCEPT p_start_date PROMPT 'What is the start date (MM/DD/YYYY): ';
ACCEPT p_end_date PROMPT 'What is the end date (MM/DD/YYYY): ';


DECLARE
v_job emp.job%TYPE;
v_empno emp.empno%TYPE := &p_emp_id;
v_start_date VARCHAR2(12) := '&p_start_date';
v_end_date VARCHAR(12) := '&p_end_date';
v_itemcomm item.NUMBER(9,2) :=0;
v_salesman_comm NUMBER(9,2) :=0;



/* cursor to get get customer ids from repid */

CURSOR get_cust_id_cursor IS

SELECT custid
FROM customer
WHERE repid = v_empno;

/* cursor to get order ids from customer ids */

CURSOR get_order_id_cursor (v_custid NUMBER) IS

SELECT ordid
FROM ord
WHERE custid = v_custid AND (orderdate BETWEEN TO_DATE(v_start_date ,'MM/DD/RRRR') AND TO_DATE(v_end_date,'MM/DD/RRRR'));

/* cursor to add item totals for separate orders */

CURSOR item_tot_cursor (v_ord_id NUMBER) IS

SELECT itemtot, commplan
FROM item
WHERE ordid = v_ord_id;

/* cursor to select all orders where commplan is NULL (for the big cheese bonus) */

CURSOR bigcheese_bonus_cursor IS

SELECT ordid
FROM ord
WHERE commplan is NULL
AND (orderdate BETWEEN TO_DATE(v_start_date ,'MM/DD/RRRR') AND TO_DATE(v_end_date,'MM/DD/RRRR'));


BEGIN
SELECT job INTO v_job
FROM emp
WHERE empno = v_empno;

BEGIN
IF v_job = 'SALESMAN' THEN

FOR get_cust_id_record IN get_cust_id_cursor LOOP
FOR get_order_id_record IN get_order_id_cursor
(get_cust_id_record.custid) LOOP
FOR item_tot_record IN item_tot_cursor
(get_order_id_record.ordid) LOOP

IF item_tot_record.commplan = 'A' THEN
v_salesman_comm := v_salesman_comm + item_tot_record.itemtot * 0.05;

ELSEIF item_tot_record.commplan = 'B'THEN
v_salesman_comm := v_salesman_comm + item_tot_record.itemtot * 0.10;

ELSEIF item_tot_record.commplan = 'C' THEN
v_salesman_comm := v_salesman_comm + item_tot_record.itemtot * 0.15;

ELSEIF item_tot_record.commplan IS NULL THEN
v_salesman_comm := v_salesman_comm;

END IF;

END LOOP;
END LOOP;
END LOOP;



ELSEIF v_job = 'PRESIDENT' THEN


FOR get_cust_id_record IN get_cust_id_cursor LOOP
FOR get_order_id_record IN big_cheese_bonus_cursor
(get_cust_id_record.custid) LOOP
FOR item_tot_record IN item_tot_cursor
(get_order_id_record.ordid) LOOP

v_item_comm := (item_tot_record.itemtot * 0.20);
v_salesman_comm := v_salesman_comm + v_item_comm;

END LOOP;
END LOOP;
END LOOP;


ELSE

DBMS_OUTPUT.PUT_LINE('The employee is not eligible for a comission');

END IF;

END;

DBMS_OUTPUT.PUT_LINE('For Employee #: '||v_empno||' The total commision is: '||v_salesman_comm);



EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(SQLCODE);

END;
/
Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com

 

[TheLexx]

You would appear to have a syntax error, elseif should be elsif.