I have a population and I need to loop through the population, get the reject codes from another table. and insert the reject codes into the population. The following is the code I have so far. It will display the correct name (it loops through the population getting the different names) but it gets the first record reject code (if it exists, if not, it goes to the else statement) and keeps that for each record it loops through. I do not understand why, if it looping through the population, why it is not getting the different reject codes.
For example, if the first record does not exist, it will put Not 1, Not 2, Not 3 as ALL reject codes. If the first record does exist and the reject codes are 1, 2, 3, it will put 1, 2, 3 for ALL reject codes. Any idea what I am doing wrong or how I can do this easier/better?
Thank you!
SQL
set serveroutput on
declare
CURSOR c_dataPopulation IS
SELECT
ROWID,
YR Year,
LASTNAME ,
FIRSTNAME ,
MI ,
ZIP ,
NULL "Reject Reason"
from data
where YR = 2023
order by 3,4,5 ;
v_rej1 VARCHAR2(60) := null;
v_rej2 VARCHAR2(60) := null;
v_rej3 VARCHAR2(60) := null;
v_rec_exists NUMBER := 0;
v_ssn NUMBER := 0;
BEGIN
FOR rec_data in c_dataPopulation
LOOP
SELECT count(p.ssn)
INTO v_rec_exists
FROM people p
WHERE p.lastname = rec_data.lastname
AND p.firstname = rec_data.firstname
AND p.mi = rec_data.mi
;
IF v_rec_exists > 0 THEN
SELECT p.ssn, nvl(p.rej1, 'NA'), NVL(p.rej2, 'NA'), NVL(p.rej3, 'NA')
INTO v_ssn, v_rej1, v_rej2, v_rej3
FROM people p
WHERE p.lastname = rec_data.lastname
AND p.firstname = rec_data.firstname
AND p.mi = rec_data.mi
;
ELSE if v_rec_exists = 0 THEN
v_rej1 := 'Not1';
v_rej2 := 'Not2';
v_rej3 := 'Not3';
END IF;
DBMS_OUTPUT.PUT_LINE('Name is '|| rec_data.lastname || ' ' || rec_data.firstname || ' ' || rec_data.mi);
DBMS_OUTPUT.PUT_LINE('Rej1 is '|| v_rej1);
DBMS_OUTPUT.PUT_LINE('Rej2 is '|| v_rej2);
DBMS_OUTPUT.PUT_LINE('Rej3 is '|| v_rej3);
DBMS_OUTPUT.PUT_LINE('Count is '|| v_rec_exists);
v_rec_exists := 0;
v_rej1 := null;
v_rej2 := null;
v_rej3 := null;
END LOOP;
END ;