# How can I return the number of values in each row which match a criterion?

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

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

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
-
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! – Niall Litchfield 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. – Jack Douglas Dec 22 '11 at 15:48
@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. – Leigh Riffel Dec 22 '11 at 16:11