Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Selecting Range values

    Hi,

    I have a table data as below mentioned.

    OBJ_ID A_VAL B_VAL TOTAL
    -------- ------ ------ -------
    1049921 20 1500 1520
    1049921 80 4050 4130
    3307364 10 1000 1010
    3307364 50 2050 2100
    3307364 100 5500 5600

    I would like to select the obj_id based on the range (input) been specified.
    E.g If am entering my input as 1501 (which is between 1500 and 1520 in the first row) it should return obj_id as 1049921, if i enter 5550 it should return 3307364 and more importantly it should not return any other obj_id apart from this.
    Apart from this, if i input a range which is greater than TOTAL column (Considering the 1st row once again) like 1521 or less than B_VAL like 1499, it should not return any value.

    Awaiting for your speedy response.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    What about simple math?
    Code:
    WHERE <input> >= A_VAL AND <input> <= B_VAL
    (assuming you swapped columns in given sample, so TOTAL values are in second data column).
    Of course, intervals cannot overlap, but this is more logical problem than Oracle one.

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    Still it dint worked as it is returning obj_id's which is below or above the specified range.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I could be confused, but why not a simple

    my_input between a_val and b_val
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    What do you mean it doesn't work?

    Code:
    SQL> create table t (OBJ_ID number(7),
      2                  A_VAL  number(3),
      3                  B_VAL  number(4));
    
    Table created.
    
    SQL> insert into t values (1049921, 20, 1500);
    SQL> insert into t values (1049921, 80, 4050);
    SQL> insert into t values (3307364, 10, 1000);
    SQL> insert into t values (1049921, 50, 2050);
    SQL> insert into t values (1049921, 100, 5500);
    5 rows created.
    
    SQL> select distinct OBJ_ID
      2  from t 
      3  where 1510 between B_VAL and (B_VAL+A_VAL);
    
        OBJ_ID
    ----------
       1049921

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •