Home » SQL & PL/SQL » SQL & PL/SQL » Why this random drawing doesn't do the thing intended
Why this random drawing doesn't do the thing intended [message #256053] Thu, 02 August 2007 20:40 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
Hi,

I've written this procedure to basically assign a random sequence to a group of records of interest (IRTEAM = FSC).

Let's say there're 12 records to be assigned. I always ended up getting stuck at the point where I inserted 11 records and and it goes on and on and on without stopping. The 12th random_no (head_cnt) is drawn, but it's just that it doesn't go into the loop because duplicate is always back to 1 after being resetted to 0.

Here is the code.
CREATE OR REPLACE
    PROCEDURE IRleadsApportioning AS

    --TYPE NumList IS TABLE OF NUMBER;          
    leads number;
    N number;
    N2 number;                            
    duplicate integer := 0;
    nl NumList := NumList(0) ;
    nl_2 NumList;
    workload NUMBER;
    sales_agent_id IB_TECH_APPS_TEMPLATE.IRTEAM_MEMBER%TYPE;    
    inserted INTEGER := 0;
    random_no number := 0;
    index_I number;
    FSC IB_TECH_APPS.FOLLOWUP_SALES_CHANNEL%TYPE;
    IR_C number; 
    head_cnt number;
    
   
    type curtype is ref cursor;
    
    cur_2 curtype;
    cur_1 curtype;
    
    sumry_row IB_TECH_APPS_SUMMARY%ROWTYPE;
    IB_2_row IR_TEAM_MEMBER_MAP%ROWTYPE;
    
    subtype sqltype is varchar2(1000);
    SQL_Statement sqltype; 
     
    IR_ID  IR_TEAM_MEMBER_MAP_2.IR_ID%TYPE;
    COUNTRY  IR_TEAM_MEMBER_MAP_2.COUNTRY%TYPE;
    IRTEAM  IR_TEAM_MEMBER_MAP_2.IRTEAM%TYPE;
    IRTEAM_MEMBER  IR_TEAM_MEMBER_MAP_2.IRTEAM_MEMBER%TYPE;
    IRTEAM_ALLOCATION  IR_TEAM_MEMBER_MAP_2.IRTEAM_ALLOCATION%TYPE;

    CURSOR IB 
         IS 
           SELECT a.* FROM IB_TECH_APPS a;


     CURSOR IB_2 (IR_TEAM_NAME IN CHAR)
          IS
          
        SELECT * FROM (
            SELECT a.*, rownum AS ROW_NO FROM IR_TEAM_MEMBER_MAP a 
            WHERE IRTEAM = IR_TEAM_NAME
            AND IRTEAM_ALLOCATION != 0
            ) ;
            

    CURSOR IB_3 (sales_agent_id IN VARCHAR2)
        IS
        
        SELECT COUNT(*) FROM IB_TECH_APPS_TEMPLATE 
        WHERE TRIM(IRTEAM_MEMBER) = sales_agent_id
        AND random_no != 0 
        AND random_no IS NOT NULL;
    
    CURSOR IB_4 (FSC IN VARCHAR2)
        IS
        
        SELECT COUNT(*) FROM IR_TEAM_MEMBER_MAP_2 
        WHERE IRTEAM = FSC
        AND HEAD_CNT != 0; 
        

BEGIN

     FOR IB_leads in IB LOOP
          
         FSC := IB_leads.FOLLOWUP_SALES_CHANNEL;
        
        DBMS_OUTPUT.PUT_LINE ( 'FSC = ' || FSC );
     
        OPEN cur_2 for SELECT * FROM IB_TECH_APPS_SUMMARY
                            WHERE IRTEAM = FSC;
        

         FETCH cur_2 INTO sumry_row;
         IF cur_2%FOUND
         THEN 

         N := sumry_row.IR_COUNTS; 
         DBMS_OUTPUT.PUT_LINE ( 'N = ' || N );
         
        /* 2 scenarios: 
        leads <= IR_TEAM_MEMBERS 
        
        use draw_wo_replacement to randomize drawing  
        
        leads > IR_TEAM_MEMBERS (n) 
    
        #_of_leads mod n 
        */
        
         WHILE (duplicate = 0 AND N >= inserted) 
         LOOP
               SELECT rand_gen (N) INTO N2 FROM dual; 
               
               DBMS_OUTPUT.PUT_LINE ( ' N2 = ' ||  N2 );
               
               IF EXISTED(N2, nl)   
                  THEN  Duplicate := duplicate + 1;
                  
               ELSE nl.extend;
                    IF (nl(nl.Last) = 0 OR nl(nl.Last) IS NULL) THEN
                    nl(nl.Last) := N2;
                    END IF;
                END IF;    

                        OPEN IB_4 (FSC);
                            FETCH  IB_4 INTO INSERTED;
                            EXIT WHEN IB_4%NOTFOUND;
                            
                        CLOSE IB_4;
                         
                        DBMS_OUTPUT.PUT_LINE ( 'INSERTED = ' || INSERTED );
                        
                        OPEN cur_1 for  SELECT IR_ID, COUNTRY, IRTEAM, IRTEAM_MEMBER, 
                                               IRTEAM_ALLOCATION, HEAD_CNT
                                         FROM (
                                            SELECT a.*, rownum AS ROW_NO FROM IR_TEAM_MEMBER_MAP a 
                                            WHERE IRTEAM = FSC
                                            AND IRTEAM_ALLOCATION != 0
                                        )  WHERE ROW_NO = N2;                         
                        FETCH cur_1 INTO IB_2_row;
                        EXIT WHEN cur_1%NOTFOUND; 
                        
                        DBMS_OUTPUT.PUT_LINE( 'sumry_row.IR_COUNTS = ' || sumry_row.IR_COUNTS );
                        DBMS_OUTPUT.PUT_LINE ( 'Duplicate = ' || Duplicate );
                        
                        
                        IR_ID := IB_2_row.IR_ID;
                        COUNTRY := IB_2_row.COUNTRY;
                        IRTEAM := IB_2_row.IRTEAM;
                        IRTEAM_MEMBER := IB_2_row.IRTEAM_MEMBER;
                        IRTEAM_ALLOCATION := IB_2_row.IRTEAM_ALLOCATION;
                        
                        IF (sumry_row.IR_COUNTS >= inserted AND Duplicate = 0) 
                        
                         THEN SQL_statement := 'INSERT INTO IR_TEAM_MEMBER_MAP_2 ' ||
                                                'VALUES ( '||  IR_ID || ', ' ||  
                                                          '''' ||  COUNTRY || ''', ' ||                                                           
                                                          ''''||  IRTEAM || ''', ' ||
                                                          '''' ||  IRTEAM_MEMBER || ''', '  
                                                           ||  IRTEAM_ALLOCATION || ', '  
                                                           ||' :head_cnt  ) '  ;
                                                          
                        execute immediate SQL_statement using N2;
                        
                        dbms_output.put_line('head_cnt ' || N2 || ' inserted');
                        COMMIT;
                 
                        Elsif (Duplicate >= 1 ) THEN 
                          
                            duplicate := duplicate - 1;
                        DBMS_OUTPUT.PUT_LINE ( 'duplicate (1) = ' || duplicate );  
                        
                        END IF;
                        
                        CLOSE cur_1;
         END LOOP;
         
         nl.DELETE;
         duplicate := 0;
          END IF;
     END LOOP; 

END;


Well, I am not really sure what's the most efficient way to show this program. But let me paste my dbms_output.

FSC = IB Tech BDC-EntManager
FSC = IB Tech BDC-CoreDatabase
N = 12
 N2 = 6
INSERTED = 0
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 6 inserted
 N2 = 3
INSERTED = 1
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 3 inserted
 N2 = 1
INSERTED = 2
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 7
INSERTED = 2
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 7 inserted
 N2 = 3
INSERTED = 3
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 2
INSERTED = 3
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 5
INSERTED = 3
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 5 inserted
 N2 = 2
INSERTED = 4
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 3
INSERTED = 4
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 6
INSERTED = 4
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 6 inserted
 N2 = 1
INSERTED = 5
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 12
INSERTED = 5
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 12 inserted
 N2 = 11
INSERTED = 6
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 11 inserted
 N2 = 6
INSERTED = 7
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 10
INSERTED = 7
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 10 inserted
 N2 = 7
INSERTED = 8
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 12
INSERTED = 8
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 12 inserted
 N2 = 2
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 10
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 5
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 5
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 2
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 8
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 2
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 8
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 5
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 4
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 10
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 5
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 10
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 1
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 12
INSERTED = 9
sumry_row.IR_COUNTS = 12
Duplicate = 0
head_cnt 12 inserted
 N2 = 9
INSERTED = 10
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 9
INSERTED = 10
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0
 N2 = 3
INSERTED = 10
sumry_row.IR_COUNTS = 12
Duplicate = 1
duplicate (1) = 0

[Updated on: Fri, 03 August 2007 12:57]

Report message to a moderator

Re: Why this random drawing doesn't do the thing intended [message #256069 is a reply to message #256053] Thu, 02 August 2007 21:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You haven't included the code for RAND_GEN(). Most random number generators generate a number LESS THAN the parameter.

ie. To allocate numbers between 1 and N, you may need to use RAND_GEN(N+1)

Ross Leishman
Re: Why this random drawing doesn't do the thing intended [message #256075 is a reply to message #256069] Thu, 02 August 2007 22:26 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Right. I basically used floor(dbms_random.value (1, N+1))

But look at the output. Nothing is wrong about random number generation. It's really about the part of how I stored the numbers in the numList nl. Seems to me there're duplicates in there.

Re: Why this random drawing doesn't do the thing intended [message #256090 is a reply to message #256053] Fri, 03 August 2007 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make sure that lines of code do not exceed 80 or 100 characters when you format.
EVEN BLANK LINES.

Regards
Michel
Re: Why this random drawing doesn't do the thing intended [message #256144 is a reply to message #256053] Fri, 03 August 2007 02:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've got no idea what the problem is by lokoing at the code, and without Table and data scripts I can't run it.

On inspection, I'm not certain this line is right:
FOR i IN nl.LAST .. nl.LAST
- are you sure you don't mean
FOR i IN nl.FIRST .. nl.LAST


It would help if you could post the code with some sort of consistent indenting, and remove the huge chunks of commented out code that you've got.

From a performance point of view, replace this
SELECT rand_gen (N) INTO N2 FROM dual; 
with
N2 := rand_gen (N);


Re: Why this random drawing doesn't do the thing intended [message #256284 is a reply to message #256144] Fri, 03 August 2007 12:53 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
Yes, that's correct!

I just wanted to insert the number drawn to the last element of the NumList, which I just extended. And it'll be NULL always.

Okay, code will be formatted. I thoguht it'd be helpful to show what has been tried in the dubugging process.

Previous Topic: Query Execution in UNIX AIX system
Next Topic: Error creating users in Procedure (merged)
Goto Forum:
  


Current Time: Mon Dec 05 11:06:44 CST 2016

Total time taken to generate the page: 0.16407 seconds