Is there integer ranges for Where Clause?

Question

I need to check that result of expression in where clause is in range of Integers.

something lke this: select * from table where (col1 / col2 ) in (1..8). (1..8) - means range of integers.

I mean that it must be integer, not float. So that I cant use "between 1 and 8", because 1.2 will be correct.

Are there any thought?

Answer 1

You could cast it from float to int and use between. (You might want to make a virutal/computed column depending on the query's performance.)

Answer 2

To test col1/col2 is an integer, you could Mod them together...

Where (col1/col2) = (col1\col2) and (col1/col2) between 1 and 8

Answer 3

How about

select * 
from table
where (col1 / col2 ) BETWEEN 1 AND 8
  and (col1 / col2 ) = FLOOR(col1 / col2 )

This simply checks if the fraction is in the interval, and integer.

Answer 4

You can of course do this:

select * from table where (col1 / col2 ) in (1,2,3,4,5,6,7,8);

or

select * from table where (col1 / col2 ) between 1 and 8
and mod (col1 , col2 ) = 0;

Answer 5

You can use pipeline function to generate integer range.

create or replace type my_number_collection is table of number;

create or replace function
  gen_range (p_from in number, p_to in number)
  return my_number_collection
  PIPELINED
  as
  begin
    for i in p_from..p_to loop
      pipe row(i);
    end loop;
    return;
  end;
/

select *
from my_table 
where col1/col2 in (select column_value from table(gen_range(1,8)));