6 Replies Latest reply: Feb 19, 2013 3:42 AM by Paul Horth RSS

    Logic

    988253
      Hello EVERYONE,

      I want set logic to brake a SERIES.
      I have ONE column in my table and in that column there are unique serial numbers

      SRL_NO - COLUMN
      A123
      A124
      A125
      A127
      A128
      A129


      A123-A125 is my 1st group or u can say 1series
      i want to brake series after A125...
      SO MY SECOND SERIES WOULD BE A127-A129
      CAUSE THERE IS ABSENCE OF A126 AND THATS THE REASON I WANT TO BREAK THE SERIES


      N MAIN THING IS USER IS GENERATING THAT SERIAL NUMBERS THROUGH ONE FORM.SO IT CAN BE ANYTHING

      SO I CAN SET THE LOGIC

      PLEASE GET ME SOME HINTS
        • 1. Re: Logic
          jeneesh
          985250 wrote:
          PLEASE GET ME SOME HINTS
          with t as
          (
          select 'A123'  srl_no from dual union all
          select 'A124' from dual union all
          select 'A125' from dual union all
          select 'A127' from dual union all
          select 'A128' from dual union all
          select 'A129' from dual
          )
          select min(srl_no) mn,max(srl_no) mx
          from(
            select srl_no
            from t
            order by srl_no
            )
          group by   to_number(regexp_substr(srl_no,'[0-9]+'))-rownum
          order by 1;
          
          MN   MX 
          ---- ----
          A123 A125 
          A127 A129 
          • 2. Re: Logic
            Frank Kulash
            Hi,
            985250 wrote:
            Hello EVERYONE,

            I want set logic to brake a SERIES.
            I have ONE column in my table and in that column there are unique serial numbers

            SRL_NO - COLUMN
            A123
            A124
            A125
            A127
            A128
            A129


            A123-A125 is my 1st group or u can say 1series
            i want to brake series after A125...
            SO MY SECOND SERIES WOULD BE A127-A129
            CAUSE THERE IS ABSENCE OF A126 AND THATS THE REASON I WANT TO BREAK THE SERIES
            Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}
            N MAIN THING IS USER IS GENERATING THAT SERIAL NUMBERS THROUGH ONE FORM.SO IT CAN BE ANYTHING

            SO I CAN SET THE LOGIC

            PLEASE GET ME SOME HINTS
            See {message:id=9953384} and      {message:id=9957164}
            Your problem has an additional complication: you need to extract the number part from each string. You can do that using REGEXP_SUBSTR.
            • 3. Re: Logic
              988253
              My Table Structure with data is as follows

              DOC_NO ITEM_TYPE ITEM_CODE SRLNO_ITEM_TYPE SRLNO_ITEM_CODE SRLNO_ITEM_NAME UNIQUE_SRL_NO
              6 PR 1100100005 MM 0501000114 PUMP A1
              6      PR 1100100005 MM 0501000114 PUMP A2
              6     PR 1100100005 MM 0501000114 PUMP A4
              6     PR 1100100005 MM 0501000114 PUMP A5
              6     PR 1100100005 MM 0501000114 PUMP A6
              6     PR 1100100005 MM 0501000220 VALVE B1
              6     PR 1100100005 MM 0501000220 VALVE B2
              6     PR 1100100005 MM 0501000220 VALVE B5
              6     PR 1100100005 MM 0501000220 VALVE B6
              6     PR 1100100005 MM 0501000220 VALVE B7

              PR-PRODUCT
              MM-MATERIAL

              I want to write function.In that function DOC_NO, ITEM_TYPE, ITEM_CODE are my input parameters of function.
              my function would return output as follows

              PUMP : A1-A2 , A4-A6
              VALVE : B1-B2, B5-B7

              PLEASE GUIDE ME
              THANKS
              • 4. Re: Logic
                988253
                Please Reply me.........
                • 5. Re: Logic
                  988253
                  CAN ANYONE HELP ME????
                  • 6. Re: Logic
                    Paul  Horth
                    That's not what you were asked for. You were asked for create table statements and insert statements with the test data.

                    That way the volunteers here don't have to waste their time doing it for you. It's also no good demanding that we reply to you,
                    we're not your paid support - we're volunteers.

                    Put the effort in yourself and people will be more likely to help you. Also please re-read the FAQ, in particular the bit about wrapping your code in
                     tags to preserve the formatting.
                    
                    Also DON'T SHOUT - THAT IS CONSIDERED RUDE AS WELL.
                    
                    Finally, please make an effort to mark your previous posts as answered: this also saves our time when deciding which questions to look at.
                    
                    {quote}
                    Handle:      985250  
                    Status Level:      Newbie
                    Registered:      Jan 30, 2013
                    Total Posts:      26
                    Total Questions:      9 (7 unresolved)
                    {quote}
                    
                    Edited by: Paul  Horth on Feb 19, 2013 9:40 AM