Greeting, I have a beginner experience with programming in Oracle PL/SQL. The problem I am trying to solve is that in a hypothetical situation, I want to compare records in the same table based on the arbitrary columns called effdt(effective date) and position_nbr(position number). They would be based on new and old records. If the the new effdt field is greater than or equal to(>=) the old effdt based on the old and new position_nbr being equal, I want to delete the old effdt record from the table. Below is the small procedure I have developed to execute.
DECLARE table_cnt_001 NUMBER; CURSOR ITR1 IS (SELECT * FROM TABLE1 O INNER JOIN TABLE1 N ON N.POSITION_NBR = O.POSITION_NBR WHERE N.EFFDT >= O.EFFDT); BEGIN SELECT COUNT(*) INTO effdt_cnt_001 FROM TABLE1; --Checking if table is empty IF table_cnt_001 !=0 THEN FOR IT IN ITR1 LOOP --BEGIN IF (N.EFFDT >= O.EFFDT) AND (N.POSITION_NBR = O.POSITION_NBR) THEN DELETE FROM TABLE1 O WHERE EXISTS(SELECT O.EFFDT FROM TABLE1 O); END IF; COMMIT; --END; END LOOP; END IF; END;
I have gotten the error identifier 'N.EFFDT' must be declared. As far as I am concerned, I am refencing the cursor in the for loop and I am not sure why the if statement requests for this column to be declared. Please let me know what could be wrong with this logic or if there is a better way to solve this problem. Thanks in advance.