Tutorial :Is there integer ranges for Where Clause?


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).   

With (1..8) representing a 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.


You can of course do this:

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


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


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.


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.)


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

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


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)));  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »