Home » SQL & PL/SQL » SQL & PL/SQL » How should I re-write my procedure to make the update work?
How should I re-write my procedure to make the update work? [message #217850] Mon, 05 February 2007 20:36 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
CREATE OR REPLACE
        PROCEDURE new_scored_alm_leads_proc AS
		
		
	
		l_num number;
		N number := 0;
		K number := 0;
		
		NBR_HISCORE_SENT number := 0;
		TGT_LOSCORE_2B_SENT NBR_HISCORE_SENT%TYPE := 0;
		NBR_LOWSCORE_UNSENT number := 0;
		PCT_LOSCORE_SENT number := 0;
		TOT_LOSCORE_SENT number := 0;
		--DISPOSITION VARCHAR2 (50) := NULL;
		first_rec DM_METRICS.DP_new_scored_alm_leads_3.lead_id%TYPE := 0;
		last_rec DM_METRICS.DP_new_scored_alm_leads_3.lead_id%TYPE := 0;
		MDS number := 0;
		new_DISP DM_METRICS.DP_new_scored_alm_leads_3.DISPOSITION%TYPE;
		cur_lead_id DM_METRICS.DP_new_scored_alm_leads_3.lead_id%TYPE;
				
		CURSOR C1 IS 
			      SELECT COUNT(*)  
				     FROM DM_METRICS.DP_new_scored_alm_leads_3
					    WHERE DISPOSITION = 'SENT TO INTERNAL SALES'
						     AND MODEL_DECILE_SCORE IN (8,9);

		CURSOR c2 IS 
	
    		   	  SELECT COUNT(*)
   				  		 FROM DM_METRICS.DP_new_scored_alm_leads_3
						    WHERE DISPOSITION = 'NOT SENT: LOW SCORE';
   		
		CURSOR c3 IS

			   	 SELECT lead_id, DISPOSITION, MODEL_DECILE_SCORE  
				 	FROM  DM_METRICS.DP_new_scored_alm_leads_3
					FOR UPDATE OF SEND_TO_OTS, DISPOSITION, NOTES NOWAIT ;
				 
		CURSOR c4 IS
		
				SELECT DISTINCT MIN(LEAD_ID)  OVER (ORDER BY LEAD_ID) as MIN_LEAD_ID 
							 	FROM  DM_METRICS.DP_new_scored_alm_leads_3;		   
		CURSOR c5 IS 
				 
			    SELECT DISTINCT MAX(LEAD_ID) OVER (ORDER BY LEAD_ID DESC) as MAX_LEAD_ID 
							 	FROM  DM_METRICS.DP_new_scored_alm_leads_3 ;
 
				 
				 /*
				 SELECT CASE 
				 		WHEN (LEAD_ID = MAX_LEAD_ID)
						THEN 1
						ELSE 0
						END AS last_row_ID
						FROM (SELECT LEAD_ID, MAX(LEAD_ID) OVER (ORDER BY LEAD_ID DESC) MAX_LEAD_ID 
							 	FROM  DM_METRICS.DP_new_scored_alm_leads_3 )
						ORDER BY last_row_ID;
				  */	
						
	
				 
		

				  
	BEGIN
		 OPEN C1;
		 OPEN C2;
		 OPEN C4;
		 OPEN C5;
 				
	 			 FETCH C1 INTO NBR_HISCORE_SENT    ;
				 FETCH C2 INTO NBR_LOWSCORE_UNSENT ;
				 FETCH C4 INTO first_rec;
				 FETCH C5 INTO last_rec;
				 					
				 TGT_LOSCORE_2B_SENT := CEIL(NBR_HISCORE_SENT/2);
				 dbms_random.seed('358798');
 			 	 l_num := dbms_random.random;
			 	 dbms_output.put_line(l_num);
		
		
		FOR alm_leads in C3 LOOP
	   	   SELECT DISPOSITION INTO new_DISP FROM DM_METRICS.DP_new_scored_alm_leads_3 WHERE lead_ID = alm_leads.lead_ID;
		   SELECT MODEL_DECILE_SCORE into MDS FROM DM_METRICS.DP_new_scored_alm_leads_3 WHERE lead_ID = alm_leads.lead_ID;
		   SELECT lead_id into cur_lead_id FROM DM_METRICS.DP_new_scored_alm_leads_3 WHERE lead_ID = alm_leads.lead_ID;
		   
			IF cur_lead_id = first_rec THEN 

							  N := NBR_LOWSCORE_UNSENT;
							  K := TGT_LOSCORE_2B_SENT;

			END IF;
    		IF new_DISP = 'NOT SENT: LOW SCORE' 
    		   	   AND (MDS <= 0 AND MDS <= 7) THEN 
                       
                       	        IF l_num <= K/N THEN 
                       	           K := K - 1;
                       			   
								   UPDATE DM_METRICS.DP_new_scored_alm_leads_3
								   SET SEND_TO_OTS = 'Y' ,
								       DISPOSITION = 'SENT TO INTERNAL SALES: RDM LOW SCR' ,
									    	 NOTES = 'RANDOMLY-SELECTED LOW SCORE LEAD'
								   WHERE CURRENT OF C3;

								   COMMIT;		 											 

                       			   TOT_LOSCORE_SENT := TOT_LOSCORE_SENT + 1;
    
                                   ELSE  N := N - 1 ;
    						 	END IF;
    		END IF;

			IF cur_lead_id = last_rec THEN

				 PCT_LOSCORE_SENT := (TOT_LOSCORE_SENT/(TOT_LOSCORE_SENT + NBR_HISCORE_SENT));
				  dbms_output.put_line(PCT_LOSCORE_SENT);		
			END IF;
	
		END LOOP;
		
		CLOSE C5;
		CLOSE C4;
		CLOSE C2;
		CLOSE C1;

			
	END;
	


more info here available
l_num :1033105050
PCT_LOSCORE_SENT: 0

[Updated on: Mon, 05 February 2007 20:47]

Report message to a moderator

Re: How should I re-write my procedure to make the update work? [message #217899 is a reply to message #217850] Tue, 06 February 2007 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why not tell us what's the problem?
Re: How should I re-write my procedure to make the update work? [message #218062 is a reply to message #217899] Tue, 06 February 2007 11:14 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Sorry I was too tired to post the details.

Basically it seemed it never got into the part
IF l_num <= K/N THEN

So I don't see updates were done even the procedure were successfully completed.

Let me if there're other details that I could post and would help solving this issue.

Re: How should I re-write my procedure to make the update work? [message #218085 is a reply to message #217850] Tue, 06 February 2007 14:48 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
LMFA0 ..

Too tired to post the details ??

I'd guess everyone here is too tired to help then
Re: How should I re-write my procedure to make the update work? [message #218094 is a reply to message #217850] Tue, 06 February 2007 15:28 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Sorry, bad message

[Updated on: Tue, 06 February 2007 15:28]

Report message to a moderator

Re: How should I re-write my procedure to make the update work? [message #218125 is a reply to message #218094] Tue, 06 February 2007 22:50 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
It was compiled valid.
Re: How should I re-write my procedure to make the update work? [message #218127 is a reply to message #217850] Tue, 06 February 2007 22:59 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just a few remarks on the code.
- there is no need of cursors c4 and c5; an aggregation in the code does the same
SELECT MIN(LEAD_ID), MAX(LEAD_ID) INTO first_rec, last_rec
FROM  DM_METRICS.DP_new_scored_alm_leads_3;

- in the start of the FOR LOOP, you select values instead of simple assignment (or you can use it directly)
new_DISP := alm_leads.DISPOSITION;
MDS := alm_leads.MODEL_DECILE_SCORE;
cur_lead_id := alm_leads.lead_id;

- you did not specify any ORDER BY in c3; however I do not know what you want to achieve, so maybe it is ok

For further investigation, just write down (by dbms_output) the actual values before the END LOOP and compare it with the ones you suppose to get.

[Update: corrected 2nd code and comments slightly rephrased, seems I am too tired too]

[Updated on: Tue, 06 February 2007 23:14]

Report message to a moderator

Re: How should I re-write my procedure to make the update work? [message #218136 is a reply to message #218127] Tue, 06 February 2007 23:41 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Thank you so much.
Re: How should I re-write my procedure to make the update work? [message #220469 is a reply to message #218127] Tue, 20 February 2007 15:41 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
I tried to do the assignment as suggested by you. I got a bunch of errors PLS-00225: subprogram or cursor 'C3' reference is out of scope.

Do you think if it's allowed or not ?

-DP
Re: How should I re-write my procedure to make the update work? [message #220478 is a reply to message #220469] Tue, 20 February 2007 16:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I suppose you have read the first version (posted to you). However it was wrong (as I got too tired too) so I corrected it and it is visible in this thread. Try the updated version (alm_leads instead of c3).
I think your main problem is not specifying order in cursor c3, so the rows are treated in the wrong order.
Re: How should I re-write my procedure to make the update work? [message #220480 is a reply to message #220478] Tue, 20 February 2007 16:55 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
Sorry, my mistake.
I should have checked the corrected version.

Thanks.

[Updated on: Tue, 20 February 2007 16:58]

Report message to a moderator

Previous Topic: Handling Special Characters in oracle
Next Topic: PLS-00103
Goto Forum:
  


Current Time: Thu Dec 08 04:08:02 CST 2016

Total time taken to generate the page: 0.10821 seconds