Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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;
share|improve this answer
add comment

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
share|improve this answer
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! –  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
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. –  Leigh Riffel Dec 22 '11 at 16:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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