How to return the number of values in each row which match a criterion

oraclequery

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.

Best Answer

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;

commit;

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

/* 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;
Related Question