1 2 Previous Next 16 Replies Latest reply: Nov 23, 2012 6:05 AM by ranit B RSS

    Grouping

    934026
      Re: Logic--
      Posted: Nov 22, 2012 10:00 PM in response to: 3360 Reply

      Hi,Frank,or Anyone,

      Sorry for not providing the correct script that day.


      Create table Tbl_series(id number(32),Name varchar2(32))

      insert into Tbl_series(id, Name)values(1,'A');

      insert into Tbl_series(id, Name)values( 917048712,'A,Carmine');





      insert into Tbl_series(id, Name)values( 917064586,'A, Marie');

      insert into Tbl_series(id, Name)values (920905802 ,' A , Carmine');

      insert into Tbl_series(id, Name)values (920905809,'Maria A');

      insert into Tbl_series(id, Name)values (10,'B');

      insert into Tbl_series(id, Name)values(C9S-000191,'BC');

      insert into Tbl_series(id, Name)values(C9S000299,'Alt');


      insert into Tbl_series(id, Name)values (100, 'Con') ;

      insert into Tbl_series(id, Name)values(188021,     'Conret A');


      insert into Tbl_series(id, Name)values (188022     ,'Mar- IRA');


      Id     Name
      --------------------------
      1     A
      917048712     A, Carmine
      917064586     A, Marie
      920905802     A , Carmine
      920905809     Maria A
      10     B
      C9S-000191     BC
      C9S000299     Alt
      100     Con
      188021     Conret A
      188022     Mar- IRA

      Required op
      GroupId     Series     Name
      --------------------------------------
      1     1     A
      1     917048712     A, Carmine
      1     917064586     A, Marie
      1     920905802     A , Carmine
      1     920905809     Maria A
      10     10     B
      10     C9S-000191     BC
      10     C9S000299     Alt
      100     100     Con
      100     188021     Conret A
      100     188022     Mar- IRA

      Basically I am trying to group the data based upon the groupid
      Hope this requirement of mine is more legible and clear to many thanks Giri

      Edited by: Geeree on Nov 22, 2012 10:02 PM
        • 1. Re: Grouping
          sukhijank
          Hi Geeree,

          How is the row for groupid getting identified in your table?

          Regards,
          Naresh
          • 2. Re: Grouping
            ranit B
            Geeree wrote:
            Re: Logic--
            Posted: Nov 22, 2012 10:00 PM in response to: 3360 Reply

            Hi,Frank,or Anyone,

            Sorry for not providing the correct script that day.


            Create table Tbl_series(id number(32),Name varchar2(32))

            insert into Tbl_series(id, Name)values(1,'A');

            insert into Tbl_series(id, Name)values( 917048712,'A,Carmine');





            insert into Tbl_series(id, Name)values( 917064586,'A, Marie');

            insert into Tbl_series(id, Name)values (920905802 ,' A , Carmine');

            insert into Tbl_series(id, Name)values (920905809,'Maria A');

            insert into Tbl_series(id, Name)values (10,'B');

            insert into Tbl_series(id, Name)values(C9S-000191,'BC');

            insert into Tbl_series(id, Name)values(C9S000299,'Alt');


            insert into Tbl_series(id, Name)values (100, 'Con') ;

            insert into Tbl_series(id, Name)values(188021,     'Conret A');


            insert into Tbl_series(id, Name)values (188022     ,'Mar- IRA');


            Id     Name
            --------------------------
            1     A
            917048712     A, Carmine
            917064586     A, Marie
            920905802     A , Carmine
            920905809     Maria A
            10     B
            C9S-000191     BC
            C9S000299     Alt
            100     Con
            188021     Conret A
            188022     Mar- IRA

            Required op
            GroupId     Series     Name
            --------------------------------------
            1     1     A
            1     917048712     A, Carmine
            1     917064586     A, Marie
            1     920905802     A , Carmine
            1     920905809     Maria A
            10     10     B
            10     C9S-000191     BC
            10     C9S000299     Alt
            100     100     Con
            100     188021     Conret A
            100     188022     Mar- IRA

            Basically I am trying to group the data based upon the groupid
            Hope this requirement of mine is more legible and clear to many thanks Giri

            Edited by: Geeree on Nov 22, 2012 10:02 PM
            I got what are you trying to do here.

            But please Check your INSERT scripts properly.
            It has got errors...
            insert into Tbl_series(id, Name)values(C9S-000191,'BC')
            Error at line 15
            ORA-00984: column not allowed here
            Could you please repost these properly?
            Ranit B.
            • 3. Re: Grouping
              934026
              Hi Naresh,
              Its based on the name.
              Giri
              • 4. Re: Grouping
                934026
                Create table Tbl_series(id number(32),Name varchar2(32))

                error i suppose is due to alphabets in it
                • 5. Re: Grouping
                  ranit B
                  Geeree wrote:
                  Create table Tbl_series(id number(32),Name varchar2(32))

                  error i suppose is due to alphabets in it
                  So, fix it dude & re-post it.
                  Help us to help you.
                  • 6. Re: Grouping
                    934026
                    Hope this scrpt will suffice.
                    Create table Tbl_series(id varchar2(22),Name varchar 2(32))
                    
                     
                    
                    insert into Tbl_series(id, Name)values('1','A');
                    
                    
                    insert into Tbl_series(id, Name)values( '917048712','A,Carmine');
                    
                    
                    
                    
                    
                    
                    insert into Tbl_series(id, Name)values(' 917064586','A, Marie');
                    
                    
                    insert into Tbl_series(id, Name)values ('920905802' ,' A , Carmine');
                    
                    
                    insert into Tbl_series(id, Name)values ('920905809','Maria A');
                    
                    
                    insert into Tbl_series(id, Name)values ('10','B');
                    
                    
                    insert into Tbl_series(id, Name)values('C9S-000191','BC');
                    
                    
                    insert into Tbl_series(id, Name)values('C9S000299','Alt');
                    
                    
                    
                    insert into Tbl_series(id, Name)values ('100', 'Con') ;
                    
                    
                    insert into Tbl_series(id, Name)values('188021', 'Conret A');
                    
                    
                    
                    insert into Tbl_series(id, Name)values ('188022' ,'Mar- IRA');
                    • 7. Re: Grouping
                      sukhijank
                      Hi Giri,

                      How is it getting identifed that

                      The below 3 records

                      -------------------------
                      10 B
                      C9S-000191 BC
                      C9S000299 Alt
                      ------------------------

                      belong to the same groupid "10"


                      Regards,
                      Naresh
                      • 8. Re: Grouping
                        ranit B
                        >
                        Required op
                        GroupId Series Name
                        1 1 A
                        1 917048712 A, Carmine
                        1 917064586 A, Marie
                        1 920905802 A , Carmine
                        1 920905809 Maria A

                        10 10 B
                        10 C9S-000191 BC
                        10 C9S000299 Alt

                        100 100 Con
                        100 188021 Conret A
                        100 188022 Mar- IRA
                        >

                        Initially, I thought it's 'Name' which is determining the Group Id.. like 1 - A, 10 - B, 100-C, 1000 - D,...
                        But please clarify how exactly is this grouped?
                        • 9. Re: Grouping
                          AlbertoFaenza
                          Hi Geree,

                          with this input data you cannot relate one record to another.

                          Problems are:
                          a) It seems that you want to group based on the order you insert in the table but Oracle does not ensure to return the row in this exact order. So unless you add a key while inserting (like 1,2,3,4) order cannot be ensured.

                          b) How do you know which is a group and which is not? Shall we assume that when id is less than a specific length is a group otherwise is a detailed records?


                          Data model seems to have flaws.

                          Regards.
                          Al
                          • 10. Re: Grouping
                            Ashu_Neo
                            Giri ,
                            I tried it with plsql, hope this help you.
                            CREATE or REPLACE type obj AS OBJECT
                            ( grpid VARCHAR2(30),
                               id   VARCHAR2(32),
                               dis_name VARCHAR2(32)
                            )
                            /
                            CREATE or REPLACE TYPE tab IS TABLE OF obj
                            / 
                            
                            CREATE or REPLACE FUNCTION fn
                            RETURN tab
                            PIPELINED 
                            IS
                            tab1 tab:= tab();
                            v_gid varchar2(32);
                            
                            BEGIN
                                   FOR i IN (SELECT * FROM tbl_series)
                                    LOOP
                                        IF i.id IN ('1','10','100') THEN   /* Can use some other logic as per your requirement, as you didnt mention any logic on the increment of grp id  */
                                           v_gid := i.id;
                                         END IF;
                                         dbms_output.put_line('v_gid -'||v_gid);
                                        PIPE ROW (obj(v_gid,i.id,i.name));   
                                    END LOOP;
                                     return ;
                                      
                            END;
                            /
                            
                            SELECT * from TABLE(fn);
                            O/P
                            ----
                            1     1     A
                            1     917048712     A,Carmine
                            1     917064586     A, Marie
                            1     920905802      A , Carmine
                            1     920905809     Maria A
                            10     10     B
                            10     C9S-000191     BC
                            10     C9S000299     Alt
                            100     100     Con
                            100     188021     Conret A
                            100     188022     Mar- IRA
                            Thanks!

                            Edited by: Ashu_Neo on Nov 23, 2012 3:15 PM
                            • 11. Re: Grouping
                              Manik
                              what if id is other than 1,10 or 100 your code will change..

                              Better would be checking with 10^0, 10^1, 10^2 etc..... ( power (10,0) = 1 )

                              Ashu, as I understand the data model is not correct as Al pointed out.

                              Cheers,
                              Manik.
                              • 12. Re: Grouping
                                Ashu_Neo
                                Yes Manik,
                                what if id is other than 1,10 or 100 your code will change..
                                That's why, I have commented at that condition line, I guess you, over-read then! Even it can't be power of 10 or anything else. That PO has to decide and change it as per convenience .
                                That he can do by himself, I guess or can elaborate for Group id variations.

                                Thanks!
                                • 13. Re: Grouping
                                  934026
                                  Id     Name     GroupName
                                  ---------------------------------------------

                                  1     A      
                                  917048712     A, Carmine     Baird
                                  917064586     A, Marie     Baird
                                  920905802     A , Carmine     Baird
                                  920905809     Maria A     Baird
                                  10     B     
                                  C9S-000191     BC     House
                                  C9S000299     Alt      House
                                  100     Con      
                                  188021     Conret A     House
                                  188022     Mar- IRA     House


                                  Guys there is an additinal field that will help i suppose?(groupname)will this help in identifying the id
                                  • 14. Re: Grouping
                                    ranit B
                                    Geeree wrote:
                                    Id     Name     GroupName
                                    ---------------------------------------------

                                    1     A      
                                    917048712     A, Carmine     Baird
                                    917064586     A, Marie     Baird
                                    920905802     A , Carmine     Baird
                                    920905809     Maria A     Baird
                                    10     B     
                                    C9S-000191     BC     House
                                    C9S000299     Alt      House
                                    100     Con      
                                    188021     Conret A     House
                                    188022     Mar- IRA     House


                                    Guys there is an additinal field that will help i suppose?(groupname)will this help in identifying the id
                                    No. Now also how this identifies then ID with help of Group Name?

                                    Dude, please understand the business logic involved first and then post it here.
                                    Seems like you are confused with the requirement.
                                    1 2 Previous Next