12 Replies Latest reply: Nov 23, 2012 3:56 AM by Rosario Vigilante RSS

    no gap in rownumber

    Rosario Vigilante
      hello to all


      how can I get from the following query, consecutive(no holes, no gap) n_progress when KREG = '05', even if it select both rows with '05' and rows <> '05 ',
      that is, even if I have 10 rows of which 7 are different from 05 and 3 are equal '05 I have the numbers(n_progress)= 1,2,3,4,5,6,7

          select DISTINCT 
          case
          when KREG <> '05' then
               row_number() over (partition by K_BRANCA
            order by  CASE
               WHEN  K_ASL like '05%'  then 2
               ELSE 1
               END, K_ASL, ANNO,K_CODE   )
          when K_REGIME = '05' then    0
          end  NPROGRESS,
      ...
      ...
      thanks in advance
        • 1. Re: no gap in rownumber
          Nicosa-Oracle
          Hi,

          I imagine you could use substr (or trim maybe), but as you don't give us data to play with, and don't tell what output you want, it's hard to tell...
          • 2. Re: no gap in rownumber
            ranit B
            Rosario Vigilante wrote:
            hello to all


            how can I get from the following query, consecutive(no holes, no gap) n_progress when KREG = '05', even if it select both rows with '05' and rows <> '05 ',
            that is, even if I have 10 rows of which 7 are different from 05 and 3 are equal '05 I have the numbers(n_progress)= 1,2,3,4,5,6,7

            select DISTINCT 
            case
            when KREG <> '05' then
                 row_number() over (partition by K_BRANCA
            order by  CASE
            WHEN  K_ASL like '05%'  then 2
            ELSE 1
            END, K_ASL, ANNO,K_CODE   )
            when K_REGIME = '05' then    0
            end  NPROGRESS,
            ...
            ...
            thanks in advance
            Please explain it clearly. It's very confusing.
            Also, post sample data and help us to help you.
            • 3. Re: no gap in rownumber
              Rosario Vigilante
              Yes thanks, you're right, I'll try to explain that I want

              IF I have the rows having column kreg, a sample, like as
              kreg
              05       ....
              01       ....
              01       ....
              05       ....
              01       ....
              01       ....
              05       ....
              
              when I run the query, I can get something as
              n_progress   ...............
              0                            ...
              1                            ....
              2                            ....
              0                                ....
              3                             ....
              4                            ....
              0                              ....
              I hope do is better now

              Thanks for your time
              • 4. Re: no gap in rownumber
                bencol
                This works with an id column to order by, which you can can probably remove:
                with t (id, kreg)
                as (select 1,'05' from dual
                union all select 2,'01' from dual
                union all select 3,'01' from dual
                union all select 4,'05' from dual
                union all select 5,'01' from dual
                union all select 6,'01' from dual
                union all select 7,'05' from dual
                )
                select kreg
                      ,case WHEN kreg = '05' then 
                         0 
                       else
                         row_number() over (partition by decode(kreg,'05',1,0) order by id)
                       end new_val
                from t
                order by id
                /
                KR    NEW_VAL
                __ __________
                05          0
                01          1
                01          2
                05          0
                01          3
                01          4
                05          0
                • 5. Re: no gap in rownumber
                  Hoek
                  You could probably use mod(kreg) and row_number() or another analytical function, but we'd need something to ORDER BY.
                  Can you post the FULL PICTURE and a WORKING TESTCASE (CREATE TABLE+INSERT INTO statements).
                  (Re)read {message:id=9360002} , especially #7, 8 and 9.
                  • 6. Re: no gap in rownumber
                    Nicosa-Oracle
                    Rosario Vigilante wrote:
                    I hope do is better now
                    Not really...

                    A good habit is to give either createTable+insert, or a with_clause so that we can give a try on your real problem, and not on something that we imagine is your problem...
                    Once given this sample input data, you show what output you're trying to get, and give explanation of the logic that leads to it.

                    On the imaginary part, maybe something in that flavor ?
                    [11.2] Scott @ My11g > l
                      1  with t (grp, id)
                      2  as
                      3  (
                      4  select '05', 1 from dual union all
                      5  select '05', 4 from dual union all
                      6  select '05x', 7 from dual union all
                      7  select '07', 2 from dual union all
                      8  select '07z', 9 from dual
                      9  )
                     10  select grp, id, row_number() over (partition by substr(grp,1,2) order by id) rn
                     11* from t
                    [11.2] Scott @ My11g > /
                    
                    GRP         ID         RN
                    --- ---------- ----------
                    05           1          1
                    05           4          2
                    05x          7          3
                    07           2          1
                    07z          9          2
                    • 7. Re: no gap in rownumber
                      chris227
                      Imho you need an order over kreg, i took id for example
                      with data as (
                      select '05'  kreg , 1 id from dual union all
                      select '01', 2 from dual union all
                      select '01', 3  from dual union all
                      select '05', 4  from dual union all
                      select '01', 5  from dual union all
                      select '01', 6  from dual union all
                      select '05', 7  from dual
                      )
                      
                      select
                      case kreg
                      when '05' then 0
                      else
                      id - sum(decode(kreg, '05',1,0)) over (order by id)
                      end n
                      ,kreg
                      ,id
                      from data
                      
                      N KREG ID 
                      0 05 1 
                      1 01 2 
                      2 01 3 
                      0 05 4 
                      3 01 5 
                      4 01 6 
                      0 05 7 
                      • 8. Re: no gap in rownumber
                        Rosario Vigilante
                        Thank you to all for time and patience, apologize to everyone

                        I solved with CASE in where, the variable 'serviceambula' is given as parameter in stored procedure where my query is into, ifserviceambula = 'S' I select rows with O.K_REGIME = '05' and viceversa:
                        CASE serviceambula
                        WHEN 'N'  THEN
                           CASE
                           WHEN O.K_REGIME <> '05' THEN 1
                           WHEN O.K_REGIME =  '05' THEN 0
                           END
                        WHEN 'S'  THEN
                           CASE
                           WHEN O.K_REGIME <> '05' THEN 0
                           WHEN O.K_REGIME =  '05' THEN 1
                           END
                        END > 0
                        Thanks again

                        Edited by: Rosario Vigilante on Nov 23, 2012 10:52 AM
                        • 9. Re: no gap in rownumber
                          ranit B
                          try this... if helps..
                          with xx as(
                              select '05' kreg from dual union all
                              select '01' kreg from dual union all
                              select '01' kreg from dual union all
                              select '05' kreg from dual union all
                              select '01' kreg from dual union all
                              select '01' kreg from dual union all
                              select '05' kreg from dual 
                          )
                          select kreg,
                              case when kreg = '05' 
                                 then  0
                              else
                                  row_number() over (partition by decode(kreg,'05',0,1) order by kreg) 
                              end case    
                          from (select kreg from xx order by kreg) xx; 
                          • 10. Re: no gap in rownumber
                            AlbertoFaenza
                            Hi Rosario,

                            if you have solved your problem please mark this question as answered.

                            Your post doesn't explain how you have solved as you mentioned columns (serviceambula) which you haven't posted before.
                            Next time please post table structure and sample data.

                            People will be happy to help you are able to post information in the proper way as mentioned in SQL and PL/SQL FAQ

                            Regards.
                            Al
                            • 11. Re: no gap in rownumber
                              ranit B
                              select DISTINCT 
                              case
                              when KREG <> '05' then
                                   row_number() over (partition by K_BRANCA
                              order by  CASE
                              WHEN  K_ASL like '05%'  then 2
                              ELSE 1
                              END, K_ASL, ANNO,K_CODE   )
                              when K_REGIME = '05' then    0
                              end  NPROGRESS,
                              ...
                              ...
                              I'm still confused... what is K_REGIME ??

                              I got this.. please check ..
                              • 12. Re: no gap in rownumber
                                Rosario Vigilante
                                Grazie

                                I hope do I done what you wrote me