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.
INNER JOIN TABLE1 N
N.POSITION_NBR = O.POSITION_NBR
N.EFFDT >= O.EFFDT);
SELECT COUNT(*) INTO effdt_cnt_001 FROM TABLE1;
--Checking if table is empty
IF table_cnt_001 !=0 THEN
FOR IT IN ITR1 LOOP
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);
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.