Skip a row in my cursor!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Skip a row in my cursor!

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Skip a row in my cursor!

    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:

    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.
    Last edited by see_one; 06-15-2005 at 12:16 PM.

  2. #2
    Join Date
    May 2005
    Posts
    31
    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 (;
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    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?
    Last edited by see_one; 06-15-2005 at 02:29 PM.

  4. #4
    Join Date
    May 2005
    Posts
    31
    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'
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width