Skip a row in my cursor!

[see_one]

I have a procedure takes two IN parameter and returns one OUT parameter as a number. It starts off with OPEN, FETCH statement and if the cursor is FOUND, then it finds an agent and assign it to my OUT parameter. But I don't want to assign a number if a certain criteria is met, instead I want to skip that row completely.

This is my code:

CREATE OR REPLACE PROCEDURE DEST_TEST(GBLOC_IN IN CHAR, SCAC_IN IN CHAR,AGENT_OUT OUT NUMBER)
AS
	MIL_REC		MILAUTH_TEST%ROWTYPE;
	AGENT		NUMBER := null;
 
 CURSOR MIL_CUR IS
 SELECT * FROM MILAUTH_TEST
 WHERE GBLOC = GBLOC_IN
 AND SCAC = SCAC_IN
 AND SCAC NOT IN ('DACL','DAJF','JETF');

BEGIN
 OPEN MIL_CUR;
 FETCH MIL_CUR INTO MIL_REC;
 IF MIL_CUR%FOUND THEN
 	
    IF MIL_REC.GBLOC IN ('KPAT', 'KOAT') AND MIL_REC.AGENT_NUM = 1 THEN
         /*NEEDS TO SKIP ROW IF 'KPAT' & 'KOAT' HAS AGENT = 1*/			
          FETCH MIL_CUR INTO MIL_REC;
     ELSE
  	  AGENT := MIL_REC.AGENT_NUM;
     END IF;
  		DBMS_OUTPUT.PUT_LINE('START: SCAC ' || MIL_REC.SCAC || ',' || 'AGENT: ' || AGENT);

END IF;
 
 CLOSE MIL_CUR;
 --dbms_output.put_line(agent); 
AGENT_OUT := AGENT;
DBMS_OUTPUT.PUT_LINE('END: SCAC ' || MIL_REC.SCAC || ',' || 'AGENT: ' || AGENT);
END DEST_TEST;

If you look IF statement inside of CURSOR FOUND, I don't want to read or assign an agent if that condition is met for the row.
As of now, it still assigns an empty string, i guess. I want to go the next record, thats all i need to know and I cannot put condition in my select statement, otherwise it will skip all KPAT & KOAT. Thanks.

[Bonker]

Why don't you change cursor definition as under

CURSOR MIL_CUR IS

SELECT * FROM MILAUTH_TEST
WHERE GBLOC = GBLOC_IN
AND SCAC = SCAC_IN
AND SCAC NOT IN ('DACL','DAJF','JETF')
and decode(GBLOC,'KPAT',AGENT_NUM,decode(GBLOC,'KOAT',AGENT_NUM,2)) != 1;

if you are using Oracle 9i then

SELECT * FROM MILAUTH_TEST
WHERE GBLOC = GBLOC_IN
AND SCAC = SCAC_IN
AND SCAC NOT IN ('DACL','DAJF','JETF')
and case when GBLOC in ('KPAT', 'KOAT') then agent_num else 2 end ! = 1 (;

[see_one]

Hey,

Thanks a lot. I learned something. I am using 8i, by the way. I know the decode function but i don't understand the 2 at the very end. I am asuming it's a default value. But why? Could u explain, if u don't mind? Thanks a lot. It works fine.

P.S. Do you have book that has tricks like this or just mind work?

[Bonker]

I know the decode function but i don't understand the 2 at the very end. I am asuming it's a default value. But why? Could u explain, if u don't mind?

yes the 2 is default value so that it will select all the records if the GBLOC is not in 'KPAT', 'KOAT'