-
I think I'm going brain dead. How can a row be both "found" and "not found"? I'm obviously missing something basic here. My eventual goal is to count rows where a match is not found, but it seems every one is both found and not found. Here is the code, with results from running it after:
CREATE OR REPLACE PROCEDURE TEST
AS
v_coy NUMBER(2) := 01; /* set as needed for test or prod */
v_sql NUMBER(7);
v_text VARCHAR2(1000);
v_counter_1 NUMBER(7) := 0;
v_counter_2 NUMBER(7) := 0;
v_counter_3 NUMBER(7) := 0;
v_spcur_sp_id NUMBER(10);
v_spcur_premise_id NUMBER(10);
v_spcur_name VARCHAR2(30);
v_spcur_phone VARCHAR2(12);
v_sacur_coy NUMBER(2);
v_sacur_sa_id NUMBER(10);
v_sacur_sp_id NUMBER(10);
v_sacur_premise_id NUMBER(10);
v_sacur_account_id NUMBER(10);
CURSOR sp_cur
IS
SELECT sp_id,
premise_id,
std_format_name,
ph_primary_nbr
FROM TABLE A
WHERE premise_id between 555555555 and 555599999;
CURSOR sa_sp_cur
IS
SELECT *
FROM TABLE B
WHERE coy = v_coy
AND premise_id = v_spcur_premise_id
AND sp_id = v_spcur_sp_id;
BEGIN
OPEN sp_cur;
LOOP
BEGIN
FETCH sp_cur INTO
v_spcur_sp_id,
v_spcur_premise_id,
v_spcur_name,
v_spcur_phone;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM || ' Error on fetch from TABLE A');
END;
IF sp_cur%NOTFOUND THEN
EXIT;
ELSE
v_counter_1 := v_counter_1 + 1; /* count how many rows processed in outer loop */
END IF;
/* for testing: */
dbms_output.put_line('looping thru sp_cur with: prem: ' || v_spcur_premise_id
|| ' sp: ' || v_spcur_sp_id || ' ' || v_spcur_name
|| ' ' || v_spcur_phone);
OPEN sa_sp_cur;
LOOP
BEGIN
FETCH sa_sp_cur INTO
v_sacur_coy,
v_sacur_sa_id,
v_sacur_sp_id,
v_sacur_premise_id,
v_sacur_account_id;
EXCEPTION
WHEN OTHERS THEN
v_text := SQLERRM;
DBMS_OUTPUT.PUT_LINE(v_text || ' Error on select from TABLE B');
END;
IF sa_sp_cur%NOTFOUND THEN
v_counter_2 := v_counter_2 + 1; /* increment counter where match not found */
EXIT;
else
v_counter_3 := v_counter_3 + 1; /* increment counter where match found */
END IF;
END LOOP;
CLOSE sa_sp_cur;
END LOOP;
CLOSE sp_cur;
DBMS_OUTPUT.PUT_LINE('Total TABLE A records read: ' || v_counter_1);
DBMS_OUTPUT.PUT_LINE('No matches found on prem# AND sp# matches from TABLE B: ' || v_counter_2);
DBMS_OUTPUT.PUT_LINE('Matches found on prem# AND sp# matches from TABLE B: ' || v_counter_3);
END TEST;
/
RESULTS WHEN RUN:
SQL> exec gis.test;
looping thru sp_cur with: prem: 555595204 sp: 555598235 DOE, JOHN
Total TABLE A records read: 1
No matches found on prem# AND sp# matches from TABLE B: 1
Matches found on prem# AND sp# matches from TABLE B: 1
PL/SQL procedure successfully completed.
I know I am only reading one row in the outer loop, as a select on the table has shown. Any help is greatly appreciated.
-
I think it's the following snippet of code in your inner loop:
IF sa_sp_cur%NOTFOUND THEN
v_counter_2 := v_counter_2 + 1; /* increment counter where match not found */
EXIT;
else
v_counter_3 := v_counter_3 + 1; /* increment counter where match found */
END IF;
You will always increment v_counter_2 even if you do find a row. First time thorugh the inner loop, you find a row and increment v_counter_3. You will restart the loop, see the no more rows are found and then increment v_counter_2.
I would suggest using %ROWCOUNT in tandem with %NOTFOUND to see if you should set v_counter_2.
HTH,
Heath
