7 Replies Latest reply: Nov 23, 2012 11:26 PM by user13179060 RSS

    Number extract between two variables

    Vedant
      Hi,

      I have two variables of number type A AND B

      A = 1000
      B =2000


      I have to extract the number between these two variables. How i can do this?




      Thanks & Regards
      Vedant
        • 1. Re: Number extract between two variables
          Manik
          search for BETWEEN in SQL oracle.
          select * from dual where 200 between 100 and 300;
          May be u want to extract random value between two numbers, here is the process.
          SELECT TRUNC (DBMS_RANDOM.VALUE (100, 300)) FROM DUAL;
          Please make your question clear for us to help.. thanks!

          Cheers,
          Manik.

          Edited by: Manik on Nov 22, 2012 12:06 PM
          • 2. Re: Number extract between two variables
            jeneesh
            If a and b are inclusive
            select a+rownum-1
            from dual
            connect by a+rownum-1 <= b;
            If they are exclusive
            select a+rownum
            from dual
            connect by a+rownum < b;
            • 3. Re: Number extract between two variables
              Ashu_Neo
              Are asking to access the values between these 2 ranges of a part of your plsql logic, then you use as below.
              Declare
              a number := 1000;
              b number := 2000;
              Begin
                  FOR i in a..b
                  LOOP
                      dbms_output.put_line (i);
                  End Loop;
              End;
              /
              Or something else! then please elaborate.

              Thanks!
              • 4. Re: Number extract between two variables
                Billy~Verreynne
                Vedant wrote:
                Hi,

                I have two variables of number type A AND B

                A = 1000
                B =2000


                I have to extract the number between these two variables. How i can do this?
                Do you mean extracting the numbers from the number range that is defined by these 2 variables?

                In SQL, that can be done using recursion:
                SQL> var nStart number
                SQL> var nEnd number
                SQL> 
                SQL> begin
                  2          :nStart := 213;
                  3          :nEnd := 234;
                  4  end;
                  5  /
                
                PL/SQL procedure successfully completed.
                
                SQL> 
                SQL> 
                SQL> with number_list( n ) as(
                  2          select
                  3                  :nStart
                  4          from dual
                  5          union all
                  6          select
                  7                  n+1
                  8          from    number_list
                  9          where   n < :nEnd
                 10  )
                 11  select * from number_list;
                
                         N
                ----------
                       213
                       214
                       215
                       216
                       217
                       218
                       219
                       220
                       221
                       222
                       223
                       224
                       225
                       226
                       227
                       228
                       229
                       230
                       231
                       232
                       233
                       234
                
                22 rows selected.
                
                SQL> 
                • 5. Re: Number extract between two variables
                  ScottishPower
                  select level+&a-1 from dual connect by level <=&b-&a
                  • 6. Re: Number extract between two variables
                    Billy~Verreynne
                    ScottishPower wrote:
                    select level+&a-1 from dual connect by level <=&b-&a
                    Why basically repeat what jeneesh already offered as solution?
                    • 7. Re: Number extract between two variables
                      user13179060
                      with ts as (select min(a) a, max(a) b from (select 100 a from dual
                      union
                      select 200 a from dual))
                      select a-1+level from ts
                      connect by level <=(b+1)-a