Using IF THEN ELSEIF inside of nested FOR loops 1

[Guinea]

I have nested CURSOR 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 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 my syntax error.

I 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?

My code is pasted below

Thanks for your help!!!
-Marc

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

 

[carp]

Well, for starters, you're misspelling the word.
It's
ELSIF,
not
ELSEIF.

Try fixing this and see if it makes a difference.

 

[Guinea]

DOH!!!

THANKS!! I WILL TRY THAT and see what happens.

-MARC Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com

 

[Guinea]

That another a couple of other minor fixes and the script works like a charm. Thanks for your help.

-Marc Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com