Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: flagging process

    guys i have a serious problem here..

    i need to do flagging on a table but i just cant get it right... rows will be inserted into this table and what i need to do is to check daily the batch_no and the delivery_date and put a flag as 1,2,3 and etc...

    for example
    this date is inserted yesterday (1/8/2006)

    batch_no,delivery_date,flag
    112,29/7/2006, (update the flag to 1)
    112,29/7/2006, (update the flag to 1)
    112,30/7/2006, (update the flag to 2)

    inserted today (2/8/2006):
    112,29/7/2006, (update the flag to 3)

    this should be a loop process... it should check and update everyday, if its a new batch, then 1 and so on.... anyone can help...
    this is how i currently do it on a stored procedure but it doesnt work


    Code:
    CREATE OR REPLACE PROCEDURE SP_Insert_C02_Flagging_INV
    IS
    
    rec_count            number(38);
    v_flag_inv	number(38);
    v_new_flag	number(38);
    BEGIN
    
       BEGIN
    	  SELECT COUNT(*) 
    	  INTO rec_count
    	  FROM TRAP_RA_INV_SC_PINS_SHIPPED;
       EXCEPTION
    	  WHEN OTHERS THEN
    		dbms_output.put_line('l_errorcode: '|| SQLCODE);
       END;
       
       IF rec_count > 0 THEN
    	  BEGIN
    	    DECLARE
    	    CURSOR c2_cur IS
    
    		  SELECT BATCH_NO, DELIVERY_DATE, DISTRIBUTOR_ID, DENOMINATION, FLAG
    		  FROM TRAP_RA_INV_SC_PINS_SHIPPED;
    		 
    		BEGIN
    	      FOR c2_rec IN c2_cur LOOP
    		   BEGIN
    		   v_flag_inv := 0;
    		
    			   BEGIN
    		        SELECT MAX(FLAG)
    			    INTO v_flag_inv
    			    FROM TRAP_RA_INV_SC_PINS_SHIPPED
    			    WHERE BATCH_NO = c2_rec.BATCH_NO
    			      AND DENOMINATION = c2_rec.DENOMINATION
    			      AND DISTRIBUTOR_ID = c2_rec.DISTRIBUTOR_ID
    				  AND TO_DATE(DELIVERY_DATE,'dd/MM/RRRR') <= TO_DATE(SYSDATE,'dd/MM/RRRR');
    			   EXCEPTION
    			   WHEN NO_DATA_FOUND THEN
    			    v_flag_inv := 0;
    			   END;
    			   
    			   v_new_flag := v_flag_inv + 1;
    			   
    			   BEGIN
    			      UPDATE TRAP_RA_INV_SC_PINS_SHIPPED SET
    				    FLAG = NVL(v_new_flag,0)
    				    WHERE BATCH_NO = c2_rec.BATCH_NO
    			      	AND DENOMINATION = c2_rec.DENOMINATION
    			      	AND DISTRIBUTOR_ID = c2_rec.DISTRIBUTOR_ID
    				  	AND TO_DATE(DELIVERY_DATE,'dd/MM/RRRR') <= TO_DATE(SYSDATE,'dd/MM/RRRR')
    					AND FLAG = 0;
    		          	COMMIT;
    			   EXCEPTION
    			   WHEN NO_DATA_FOUND THEN
    			     dbms_output.put_line('l_errorcode: '|| SQLCODE);
    			   END;
    		   END;
    	      END LOOP;
    	    END;
    
    	  END;	
    	END IF;
      
      
    END SP_Insert_C02_Flagging_INV;
    /
    Last edited by shatishr; 08-02-06 at 06:59.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SELECT COUNT(*) has an exception handler which is unlikely to happen, ever - this is an aggregate function and will always return *something* (0 if nothing exists in the table); the only failure might be the fact that this table doesn't exist.

    The same goes to SELECT MAX(FLAG) - it will never return NO-DATA-FOUND but NULL, so you can't trap it that way.

    I can't look through your code carefully at the moment, but you might take those suggestions into consideration and see whether you can do something about it.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also, you do not have to do a count at all. Just run the cursor loop. If the cursor doesn't return anything, then no code within the cursor will be run. I would write it as follows
    Code:
    CREATE OR REPLACE PROCEDURE SP_Insert_C02_Flagging_INV
    IS
    
    v_flag_inv    number(38);
    v_new_flag    number(38);
    
    CURSOR c2_cur IS
    SELECT BATCH_NO, DELIVERY_DATE, DISTRIBUTOR_ID, DENOMINATION, FLAG, ROWID ROW_ID
    FROM TRAP_RA_INV_SC_PINS_SHIPPED;
             
    BEGIN
      FOR c2_rec IN c2_cur LOOP
         v_flag_inv := 0;
         SELECT MAX(FLAG)
         INTO v_flag_inv
         FROM TRAP_RA_INV_SC_PINS_SHIPPED
         WHERE BATCH_NO = c2_rec.BATCH_NO
         AND DENOMINATION = c2_rec.DENOMINATION
         AND DISTRIBUTOR_ID = c2_rec.DISTRIBUTOR_ID
         AND TO_DATE(DELIVERY_DATE,'dd/MM/RRRR') <= TO_DATE(SYSDATE,'dd/MM/RRRR');
         
         IF V_FLAG_INV IS NULL THEN
           v_flag_inv := 0;
         END IF;
    
         v_new_flag := v_flag_inv + 1;
                   
         UPDATE TRAP_RA_INV_SC_PINS_SHIPPED SET
         FLAG = v_new_flag
         WHERE ROWID = C2_REC.ROW_ID;                
       END LOOP;
       COMMIT;  
      
    END SP_Insert_C02_Flagging_INV;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    little foot,
    based on your advise.. i have added nvl to the max function and also remove the date criteria on the selection part... and now it works...

    beil,
    about beil's solution.. its nice to have such a simple one but then can two rows share the same id ?

Posting Permissions

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