Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Question Unanswered: Too Many Entries !

    I have the code:

    DECLARE
    PROCEDURE inboxToSubKey (p_input_text IN VARCHAR2,
    p_option IN VARCHAR2,
    p_key_subject_id IN VARCHAR2,
    p_counter IN NUMBER) IS
    BEGIN
    IF INSTR(p_input_text, p_option) > 0 THEN
    INSERT INTO sub_res (special_Subject_id, subject_result_id, subject_result_counter)
    VALUES (sub_res_seq.NEXTVAL, p_key_subject_id, p_counter);
    ELSE
    INSERT INTO sel_sub (Selected_Subject_ID)
    VALUES ('notda');
    END IF;
    END;
    BEGIN
    << input_t_loop >>
    FOR cur_text IN (SELECT * FROM input_t) LOOP
    << sub_key_loop >>
    FOR cur_tk IN (SELECT * from sub_key) LOOP
    inboxToSubKey(cur_text.input_text, cur_tk.subject_keyword.option1, cur_tk.key_subject_id,
    cur_tk.subject_keyword_counter);
    inboxToSubKey(cur_text.input_text, cur_tk.subject_keyword.option2, cur_tk.key_subject_id,
    cur_tk.subject_keyword_counter);
    inboxToSubKey(cur_text.input_text, cur_tk.subject_keyword.option3, cur_tk.key_subject_id,
    cur_tk.subject_keyword_counter);
    END LOOP sub_key_loop;
    END LOOP input_t_loop;
    END;
    /

    The problem is this bit :

    IF INSTR(p_input_text, p_option) > 0 THEN
    INSERT INTO sub_res (special_Subject_id, subject_result_id, subject_result_counter)
    VALUES (sub_res_seq.NEXTVAL, p_key_subject_id, p_counter);
    ELSE
    INSERT INTO sel_sub (Selected_Subject_ID)
    VALUES ('notda');
    END IF;

    i only want 1 insert of 'notda' in the
    sel_sub table but it puts in as many entries as are in another table.

    Is there any way to just put one entry of 'notda' in the sel_sub table ?????

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Too Many Entries !

    If you can create a UNIQUE constraint on sel_sub.Selected_Subject_Id then only 1 insert of 'notda' will be allowed. You could catch the exception DUP_VAL_ON_INDEX and ignore it:
    PHP Code:
    ELSE
      
    BEGIN
        INSERT INTO sel_sub 
    (Selected_Subject_ID)
        
    VALUES ('notda');
      
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL
    ;
      
    END;
    END IF; 
    If that isn't possible, you could check whether the record already existed while inserting:
    PHP Code:
    ELSE
      
    INSERT INTO sel_sub (Selected_Subject_ID)
      
    SELECT 'notda' FROM DUAL
      WHERE NOT EXISTS
          
    SELECT 1
            FROM sel_sub
            WHERE Selected_Subject_id 
    'notda'
          
    );
    END IF; 

  3. #3
    Join Date
    Feb 2004
    Posts
    16

    Re: Too Many Entries !

    Thanks i made it unique and then did your first suggestion but now it does only give 1 entry of 'notda' but it now puts it in no matter what !

    any way to combat this ?

    and do i need to anything extra because of the :

    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN NULL;

    Bit ????

    oh yeah the second way didn't work at all but thanks for the suggestion anyway

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Too Many Entries !

    Sounds like your problems come from your program logic rather than from the exception handler. There is no way that that exception handler causes a record to be written that wouldn't have been written at least once before.

    Second way didn't work? Again, it is probably rather that it doesn't do what you want. Since I don't understand what you want, it's hard to say.

    Your current logic appears to be:
    Code:
    for each input_t record
      for each sub_key
         if sub_key found in record in option 1 then
            add a record to sub_res
         else
            add a 'notda' record to sel_sub (unless already exists)
         end if
         if sub_key found in record in option 2 then
            add a record to sub_res
         else
            add a 'notda' record to sel_sub (unless already exists)
         end if
         if sub_key found in record in option 3 then
            add a record to sub_res
         else
            add a 'notda' record to sel_sub (unless already exists)
         end if
      end loop
    end loop
    This means that unless every sub_key is found in every option of every input_t record, you will always end up writing a 'notda' record to sel_sub.

    Did you really mean, only write the 'notda' record id none of the sub_keys is found in any of the options in any of the input_t records? Or what?

  5. #5
    Join Date
    Feb 2004
    Posts
    16
    yes thats exactly what i meant !

    can you help me further now you have that peice of information ?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by innes_22
    yes thats exactly what i meant !

    can you help me further now you have that peice of information ?
    You only know whether or not you need the 'notda' record after you have processed all the input_t records. One way would be:
    PHP Code:
    DECLARE
       
    v_notda_required BOOLEAN := TRUE;
       
    PROCEDURE inboxToSubKey (p_input_text IN VARCHAR2,
       
    p_option IN VARCHAR2,
       
    p_key_subject_id IN VARCHAR2,
       
    p_counter IN NUMBERIS
       BEGIN
          
    IF INSTR(p_input_textp_option) > 0 THEN
             INSERT INTO sub_res 
    (special_Subject_idsubject_result_idsubject_result_counter)
             
    VALUES (sub_res_seq.NEXTVALp_key_subject_idp_counter);
             
    v_notda_required := FALSE;
          
    END IF;
       
    END;
    BEGIN
       
    << input_t_loop >>
       FOR 
    cur_text IN (SELECT FROM input_tLOOP
          
    << sub_key_loop >>
          FOR 
    cur_tk IN (SELECT from sub_keyLOOP
             inboxToSubKey
    (cur_text.input_textcur_tk.subject_keyword.option1cur_tk.key_subject_id,
                
    cur_tk.subject_keyword_counter);
             
    inboxToSubKey(cur_text.input_textcur_tk.subject_keyword.option2cur_tk.key_subject_id,
                
    cur_tk.subject_keyword_counter);
             
    inboxToSubKey(cur_text.input_textcur_tk.subject_keyword.option3cur_tk.key_subject_id,
                
    cur_tk.subject_keyword_counter);
          
    END LOOP sub_key_loop;
       
    END LOOP input_t_loop;
       IF 
    v_notda_required THEN
          INSERT INTO sel_sub 
    (Selected_Subject_ID)
          
    VALUES ('notda');
       
    END IF;
    END;

    Actually, it could probably be written more efficiently avoiding all the cursors and loops:
    PHP Code:
    BEGIN
       INSERT INTO sub_res 
    (special_Subject_idsubject_result_idsubject_result_counter)
       
    SELECT sub_res_seq.NEXTVALk.key_subject_idk.subject_keyword_counter
       FROM   sub_key k
    input_t i
       WHERE  INSTR
    i.input_textk.subject_keyword.option1 ) > 0
       
    OR     INSTRi.input_textk.subject_keyword.option2 ) > 0
       
    OR     INSTRi.input_textk.subject_keyword.option3 ) > 0;
       IF 
    SQL%ROWCOUNT 0 THEN
          INSERT INTO sel_sub 
    (Selected_Subject_ID)
          
    VALUES ('notda');
       
    END IF;
    END;


Posting Permissions

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