I have a table "trial_volts" like this in an oracle database (I think it's 10g):

trial_index NUMBER
voltage_1 NUMBER
voltage_2 NUMBER
voltage_3 NUMBER
voltage_4 NUMBER
voltage_5 NUMBER

And I want to get a list of trial_index where more than N voltages are above some threshold. I had hoped there would be some function that would let me do this:

SELECT trial_index, SOME_FUNCTION(voltage_1>10,voltage_2>10,voltage_3>10,voltage_4>10,voltage_5>10) AS vOver
FROM trial_volts
WHERE vOver>3;

...but there does not seem to be such a function. I know this table design is terrible, but I didn't design it and I can't change it, so I've got to find something that works.


I think you are going to have to roll your own.

I'd do something like the below. (last query finds first 10 records that have exactly 2 readings above my threshold).


Script for trial voltage query on DBSE


drop table voltages;

Create table voltages(
    id  number 
,   v1  number
,   v2  number
,   v3  number);

alter table voltages add constraint pk_voltages
primary key(id);

add 10,000 rows 
v1 between 0 and 10
v2 between 0 and 100
v3 between 5 and 15

insert into voltages (id,v1,v2,v3)
select rownum, dbms_random.value(0,10),dbms_random.value(0,10),dbms_random.value(0,10)
from dual
connect by level <= 10000;


create or replace function above_threshold(p_thresh in number, v1 in number, v2 in number, v3 in number)
return number
l_retval number := 0;
   if  v1 > p_thresh 
    then l_retval := l_retval + 1;
   end if;  
       v2 > p_thresh 
    then l_retval := l_retval + 1;
   end if;
       v3 > p_thresh 
    then l_retval := l_retval + 1;
  end if;
  return l_retval;

/* display those that have 2 readings above threshold */

select id,v1,v2,v3
from voltages
where above_threshold(8,v1,v2,v3) = 2
and rownum <= 10;

something like:

select * from trial_volts where case when voltage_1>10 then 1 end +
                                case when voltage_2>10 then 1 end +
                                case when voltage_3>10 then 1 end +
                                case when voltage_4>10 then 1 end +
                                case when voltage_5>10 then 1 end > 3
  • 1
    The advantage of rolling your own function as I did is that you can index it if need be. The advantage of case is that its a lot clearer to read! Dec 22 '11 at 15:43
  • Good thinking - and you can have multiple indexes for different thresholds too in the case of the function I guess. Dec 22 '11 at 15:48
  • 1
    @Niall - You can create a function based index on the case statement just as you can a defined index. Of course as Jack pointed out your function would have the configurability advantage. Dec 22 '11 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.