4 Replies Latest reply: Apr 5, 2013 2:09 PM by user8187496 RSS

    Problem grouping rows to create an id

    user8187496
      Hi all,
      I have one table(tableA) with a column called sorted_rows wich contains imported data.
      The rows looks like this,
      20AC0982034LMCODE
      60asldkf0984LMCODE
      20AC09820tyuLMCODE
      60asfkfj098674LMCODE
      20ACrtut982rt4LMCODE
      40ASDFJ34875CODE
      50SDLFK345345
      60asldkrtyyy4LMCODE
      20ACrtERT2rt4LMCODE
      40AGGHM75CODE
      50GFHJ345345
      60aslFGHFGHyy4LMCODE
      70LKDJFG34225

      One item is represented by the two first digits 20-70, the imported data is sorted so that one item for example is
      20AC0982034LMCODE
      60asldkf0984LMCODE
      the next item
      20AC09820tyuLMCODE
      60asfkfj098674LMCODE
      and so on
      the last item in the above example is
      20ACrtERT2rt4LMCODE
      40AGGHM75CODE
      50GFHJ345345
      60aslFGHFGHyy4LMCODE
      70LKDJFG34225

      What i need and can't figure out how to do is:
      starting from the first row in tableA i want to look at the two first digits 20-70 to create an item_id
      insert into tableB with an extra column item_id

      for example,
      tableA
      20AC0982034LMCODE
      60asldkf0984LMCODE
      20ACrtERT2rt4LMCODE
      40AGGHM75CODE
      50GFHJ345345
      60aslFGHFGHyy4LMCODE
      70LKDJFG34225

      new table with item_id
      20AC0982034LMCODE 101001
      60asldkf0984LMCODE 101001
      20ACrtERT2rt4LMCODE 101002
      40AGGHM75CODE 101002
      50GFHJ345345 101002
      60aslFGHFGHyy4LMCODE 101002
      70LKDJFG34225 101002

      Best regards
      Mikael
        • 1. Re: Problem grouping rows to create an id
          Ramin Hashimzadeh
          search start_of_group keyword. there is more questions and solutions about your question

          Edited by: Ramin Hashimzadeh on Apr 5, 2013 11:16 PM
          • 2. Re: Problem grouping rows to create an id
            Frank Kulash
            Hi, Mikael,

            Welcome to the forum!

            Whenever you have a problem, please post CREATE TABLE and INSERT statements for your sample data. for example:
            CREATE TABLE     tablea
            (   sorted_rows          VARCHAR2 (30)
            ,   sort_num          NUMBER (10)      PRIMARY KEY
            );
            
            INSERT INTO tablea (sorted_rows, sort_num) VALUES ('20AC0982034LMCODE',    1);
            INSERT INTO tablea (sorted_rows, sort_num) VALUES ('60asldkf0984LMCODE',   2);
            INSERT INTO tablea (sorted_rows, sort_num) VALUES ('20ACrtERT2rt4LMCODE',  3);
            INSERT INTO tablea (sorted_rows, sort_num) VALUES ('40AGGHM75CODE',         4);
            INSERT INTO tablea (sorted_rows, sort_num) VALUES ('50GFHJ345345',         5);
            INSERT INTO tablea (sorted_rows, sort_num) VALUES ('60aslFGHFGHyy4LMCODE', 6);
            INSERT INTO tablea (sorted_rows, sort_num) VALUES ('70LKDJFG34225',         7);
            Remember that rows in a table have no built-in order. For words like "first" or "next" to have any meaning, you have to say what order you mean. I'll assume you have another column in tablea (sort_num) that determines the order.

            When does a new item begin? I'm guessing that it's when the first 2 characters of sorted_rows come before the first 2 characters of the previous sorted_rows (where "previous" is determined by sort_num).

            Here's one way to do that:
            WITH     got_new_item     AS
            (
                 SELECT     sorted_rows, sort_num
                 ,     CASE
                         WHEN  SUBSTR (sorted_rows, 1, 2) >=
                                SUBSTR ( LAG (sorted_rows) OVER ( ORDER BY  sort_num)
                                 , 1
                                , 2
                                )
                         THEN  0
                         ELSE  1
                      END          AS new_item
                 FROM    tablea
            )
            SELECT    sorted_rows, sort_num
            ,       10100 + SUM (new_item) OVER (ORDER BY  sort_num)     AS item_id
            FROM       got_new_item
            ORDER BY  sort_num
            ;
            Output:
            SORTED_ROWS                      SORT_NUM    ITEM_ID
            ------------------------------ ---------- ----------
            20AC0982034LMCODE                       1      10101
            60asldkf0984LMCODE                      2      10101
            20ACrtERT2rt4LMCODE                     3      10102
            40AGGHM75CODE                           4      10102
            50GFHJ345345                            5      10102
            60aslFGHFGHyy4LMCODE                    6      10102
            70LKDJFG34225                           7      10102
            If you want to create a table that contains this result set, say:
            CREATE TABLE  tableb
            AS
            WITH     got_new_item     AS
            (
                 SELECT     sorted_rows, sort_num
            ...
            If you want to add rows to an existing tableb, then use MERGE. Exactly how to do that depends on your exact requriements. Will the item_ids always start with 10101? If not, how do you determine the first item_id to use? Will you have to check if rows from tablea have already been inserted into tableb? Is so, how will you know if two rows are the same or not? Give a couple of examples, like this:
            "If tablea contains ... and tableb contains ... before combining them, then tableb should contain ... after combining them, because .... Then, if tablea changes to ... and I combine them again, then tableb should contain ... because ...".

            See the forum FAQ {message:id=9360002}
            • 3. Re: Problem grouping rows to create an id
              onedbguru
              WHAT VERSION!!!!!

              there is a feature called substr... been around since version 1?2? In 11gR2, there is also a feature called a "derived" column that does not consume but can be indexed using a function-based index.

              alter table a add columnb generated always as substr(co1,1,2);

              This can be done on the fly if necessary.

              You know, simple questions like this can be very easily determined if one was to actually RTFineM!!!!!! You better hope that your boss doesn't search these forums to see who can't do their job and having someone else do their work for them. If you don't know how to RTFM, then you are in the wrong profession.
              • 4. Re: Problem grouping rows to create an id
                user8187496
                Hi Frank,
                Thank you for the swift reply, your reply looks very helpfull.

                Since i'm not at work anymore my database access is limited.
                I will return with results on monday.

                Also thank you for the hints about making better posts.

                Best regards
                Mikael