1

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.

3
  • Create a dbfiddle.uk that demonstrates the problem and update the post with the url – Lennart Jan 5 at 10:08
  • That is, tables and some sample data and what rows that should be deleted – Lennart Jan 5 at 10:10
  • I will try that. However, it seems dbfiddle.uk does not support oracle version 12.1 which is the version I working on – KSS_07 Jan 5 at 22:35
0

You don't need PL/SQL for this, you can delete using a sub-query:

delete from table1 where ID in (
  select ID from (
    select ID
      ,count(*) over(partition by POSITION_NBR, EFFDT) as cnt
      from table1
    )
  where cnt > 1 --more than one row matches the grouping condition
);
1
  • Thank you for providing this suggestion. What are you referring to by using an ID column/field. TABLE1 does have SEQ_ID(Sequence Id) field which is technically a unique key for every field. Please let me know if that is what you are referring to. Thanks. – KSS_07 Jan 5 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.