This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 23, 2012 4:05 AM by ranit B RSS

Grouping

934026 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    Hi Geeree,

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

    Regards,
    Naresh
  • 2. Re: Grouping
    ranit B Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Naresh,
    Its based on the name.
    Giri
  • 4. Re: Grouping
    934026 Newbie
    Currently Being Moderated
    Create table Tbl_series(id number(32),Name varchar2(32))

    error i suppose is due to alphabets in it
  • 5. Re: Grouping
    ranit B Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points