Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: Procedure to update double subcategory sequence

    Hi,

    I need to write a procedure to be able to reuse it to clean up subcategory sequence.
    Here is a problem: two tables: Category and subcategory:

    create table Category
    (category_id number,
    Name varchar2(20))
    /

    create table Sub_Category
    (sub_category_id number,
    category_id number,
    sequence number)
    /

    In the ideal world each subcategory of a single category would have unique sequence so if there are 3 subcategory for the same category then each of them would have 1,2,3 in sequence, if there are 5 subcategories then 1,2,3,4,5 for each of them etc.
    Problem I'm facing is that some of the subcategories sequences for the same category has the same values . For instance for 4 subcategories of the same category, each of them has 1 (1,1,1,1) in a sequence.

    So ideal world is :

    Insert into Category values (123 ,'Category1');
    Insert into Category values (234 ,'Category2');
    Insert into Category values (345 ,'Category3');
    Insert into Category values (456 ,'Category4');
    Insert into Category values (567 ,'Category5');



    Insert into Sub_Category values (1,123,1);
    Insert into Sub_Category values (2,123,2);
    Insert into Sub_Category values (3,123,3);

    But I've also bad rows like this:

    Insert into Sub_Category values (4,234,1);
    Insert into Sub_Category values (5,234,1);
    Insert into Sub_Category values (6,234,1);

    Insert into Sub_Category values (7,345,1);
    Insert into Sub_Category values (8,345,1);
    Insert into Sub_Category values (9,345,2);
    Insert into Sub_Category values (10,345,1);
    Insert into Sub_Category values (11,345,1);
    Insert into Sub_Category values (12,345,2);


    Insert into Sub_Category values (13,456,1);
    Insert into Sub_Category values (14,456,3);
    Insert into Sub_Category values (15,456,1);
    Insert into Sub_Category values (16,567,1);
    Insert into Sub_Category values (17,567,1);
    Insert into Sub_Category values (18,567,5);


    Fix for this and my goal is to select all such cases where subcases have mixed up sequences as above and give them randomly numbers starting from 1. So if there are 3 subcategories like for CATEGORY 2 then just apply random number to the sequence of the subCATEGORIES like 1,2,3. For CATEGORY 3 : 1,2,3 to 7.

    I was thinking to write two procedures one selecting all the categories and passing category ID to the other procedure that would actually update sequence, like this:

    CREATE OR REPLACE PROCEDURE SCHEMA.SELECT_CATEGORY
    IS
    CURSOR c1
    IS
    select category_ID from category where ...;
    BEGIN
    FOR a IN c1 LOOP
    UPDATE_SUBCATEGORY(a.Category_id);
    COMMIT;
    END LOOP;
    END;
    /


    And the actual procedure updating subcategory:



    CREATE OR REPLACE PROCEDURE SCHEMA.UPDATE_SUBCATEGORY
    BEGIN
    ............
    END;
    /

    If someone could help me write PROCEDURE SCHEMA.SELECT_CATEGORY cursor to not miss any of the categories ID having mixed up subcategory. There can be any of the doubled sequences like doubled 1 value (this is majority) but there can be any other doubles (or at least I need to make sure that there aren't any other doubles 2 values or 3 values in sequence etc.)
    And how to write SCHEMA.UPDATE_SUBCATEGORY to loop through rows of subcategory and update sequence with values starting from 1 ?

    Thanks
    Evo

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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