7 Replies Latest reply: Nov 23, 2012 6:49 AM by AlbertoFaenza RSS

    Logic--

    Geeree
      Id                          Name            Assingned/unass     gp1      gp2

      1     Ab s     3_Moderate (A)     NULL     NULL
      917048712 A l     3_Moderate (A)     Baird     NULL
      917064586     Marie     3_Moderate (A)     Baird     NULL
      920905802     Carmine     Moderate (A)     Baird     NULL
      920905809     Maria Moderate (A)     Baird     NULL
      10     Alt      UNASSIGNED (SL)     NULL     NULL
      C9S-00019 C9S-      UNASSIGNED (SL)     House     NULL
      C9S000299     Alt      UNASSIGNED (SL)     House     NULL
                                            
      CREATE TABLE  CompAccount(UniqueAccountId Varchar2(132),AccountName Varchar2(132),AssignedModelName varchar2(128),     GroupName1 varchar2(128),GroupName2 varchar2(128))
       
      
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
       (1     ,        Abs                 ,                 3_Moderate (A),       NULL     NULL)
       
      
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
      (917048712     ,Al     ,3_Moderate (A)     ,Baird     ,NULL)
       
      
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
      (917064586,     Marie       ,            3_Moderate (A),     Baird,     NULL)
       
      
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
      
      (9209      ,Abbatiello, Carmine,     3_Moderate (A)     ,Baird     ,NULL)
      
       
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
      (920      ,Maria ,      3_Mo ede  (A),     Baird     ,NULL)
      
       
      
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
      (10,                        Altm ,     UNASSIGNED (SL),     NULL,     NULL)
       
      
       
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
      C9S ,     C9S-000191     UNASSIGNED (SL),     House,     NULL
       
      
      Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
      
      (C9S00 ,     Altm      ,UNASSIGNED (SL)     ,House,     NULL)
      Hi,

      I want the o/p like this





      Id id2
      1 917048712     
      1 917064586     
      1 920905802     
      1 920905809     
      10 C9S-000191     
      10 C9S000299


      I.E. numbers that come under '1' should be against '1' and that which comes under 10 should be against 10.hope my requirement is clear.     Thank you

      Edited by: Geeree on Nov 21, 2012 7:18 AM

      Edited by: Geeree on Nov 21, 2012 7:19 AM
        • 1. Re: Logic--
          Frank Kulash
          Hi,
          Geeree wrote:
                                             
          CREATE TABLE  CompAccount(UniqueAccountId Varchar2(132),AccountName Varchar2(132),AssignedModelName varchar2(128),     GroupName1 varchar2(128),GroupName2 varchar2(128))
          Do all those columns play some role in this problem? If not, don't include the columns that don't matter.
          Insert into CompAccount(UniqueAccountId  ,AccountName V ,AssignedModelName  ,     GroupName1   ,GroupName2  )
          (1     ,        Abs                 ,                 3_Moderate (A),       NULL     NULL) ...
          Thanks for posting the CREATE TABLE and INSERT statements. Remember why you go to all that trouble: So that the people who want to help you can re-create the problem and test their ideas. If you post statements that don't work, then they don't help anybody. None of your INPUT statements work. It looks like you're missing single-quotes around the string literals. Always test (and, if necessary, correct) your statements before you post them.
          I want the o/p like this

          Id id2
          1 917048712     
          1 917064586     
          1 920905802     
          1 920905809     
          10 C9S-000191     
          10 C9S000299


          I.E. numbers that come under '1' should be against '1' and that which comes under 10 should be against 10.hope my requirement is clear.     Thank you
          What does it mean for one row to "come under" another? That is, what is there in the table that tells us 917064586 comes under 917048712, and not the other way around? How do we know that they don't both come under C9S000299? Rememeber, there is no inherent order to rows in a relational table, and the order in which rows are inserted doesn't matter.
          • 2. Re: Logic--
            Geeree
            Frank,

            My apologies,Next time I will make sure that my scripts are working :). My question is
            whether I can bring the series of number under 1 against 1 .
            i.e For example
            ID
            1
            20
            30
            40

            Is it possible to bring this like this
            ID Col2
            1 20
            1 30
            1 40
            Giri
            • 3. Re: Logic--
              Frank Kulash
              Hi, Giri,
              Geeree wrote:
              Frank,

              My apologies,Next time I will make sure that my scripts are working :).
              It's not too late to get an answer this time, if you want one.
              My question is whether I can bring the series of number under 1 against 1 .
              i.e For example
              ID
              1
              20
              30
              40

              Is it possible to bring this like this
              ID Col2
              1 20
              1 30
              1 40
              Giri
              My question is: why do you want "the series of number under 1 against 1", and not "the series of number under 20 against 20" or "the series of number under 40 against 40"? Why do you want the results you posted above, and not
              ID     Col2
              20     1
              20     30
              20     40
              or
              ID     Col2
              1      20
              30     40
              or something else? What is there in the data that says 1 is special? What says that 20 is related to 1?
              • 4. Re: Logic--
                6363
                Geeree wrote:

                My apologies,Next time I will make sure that my scripts are working :). My question is
                whether I can bring the series of number under 1 against 1 .
                You were asked to provide working SQL and a description of what you mean by under since rows in a table have no order, so none are under, over or in between any others. And you respond with next time you will post working SQL and simply repeat the question with no explanation of what you mean by under.

                Does that mean you do not want an answer to this question, just the next one you ask?
                • 5. Re: Logic--
                  Geeree
                  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
                       917048712     A, Carmine
                       917064586     A, Marie
                       920905802     A , Carmine
                       920905809     Maria A

                  10     10     B
                       C9S-000191     BC
                       C9S000299     Alt

                  100     100     Con
                       188021     Conret A
                       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
                  • 6. Re: Logic--
                    Frank Kulash
                    Hi,

                    Thanks for posting the sample data. Remember why you have to go to all that trouble: So the people who want to help you can re-create the problem and test their ideas.
                    If id is a NUMBER, then these INSERT statements
                    Geeree wrote:
                    insert into  Tbl_series(id, Name)values(C9S-000191,'BC');
                    
                    insert into  Tbl_series(id, Name)values(C9S000299,'Alt');
                    will fail. Do you want the code that we post in answers to work? Then make sure the code you post in questions works. Test (and, if necessary, correct) your code before you post it.

                    I still need an explanation of how you get the results you want from that data.
                    Required op

                    GroupId     Series     Name
                    --------------------------------------
                    1     1     A
                         917048712     A, Carmine
                         917064586     A, Marie
                         920905802     A , Carmine
                         920905809     Maria A

                    10     10     B
                         C9S-000191     BC
                         C9S000299     Alt

                    100     100     Con
                         188021     Conret A
                         188022     Mar- IRA
                    Don't take the name of the tag \
                     too literally.  It's okay to use \
                    tags to make your output readable.
                    >
                    Basically I am trying to group the data based upon the groupid
                    So what is the groupid? Why do you want the results you posted, and not
                    GroupId        Series     Name
                    --------   ----------   -------------
                    1        1          A 
                            C9S-000191     BC
                            100          Con
                    
                    917048712  917048712     A, Carmine
                             188022     Mar- IRA   
                            10          B
                    
                    188021         188021     Conret A
                    ...
                    or something else?
                    Hope this requirement of mine is more legible and clear to :) many thanks Giri
                    It's a step in the right direction. You still need to explain what you're doing, and how you get the results you want from the data posted. Remember that words like "first", "next" or "consecutive" only have meaning in the context of some ordering, so don't use words like that unless you define them. If your data is in some kind of order, explain what that is.
                    • 7. Re: Logic--
                      AlbertoFaenza
                      Hi Giri,

                      this seems to me the same question you posted here: {thread:id=2467705}

                      There is no point in posting create table and insert statement and output if you don't care about what we are asking.
                      Same questions apply here:
                      Alberto Faenza wrote:
                      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
                      Before opening a new thread try to have clear idea about the requirement.

                      Regards.
                      Al

                      Edited by: Alberto Faenza on Nov 23, 2012 1:49 PM