Hi all,

We have created a function to meet this requirement.Here is the code,

CREATE OR REPLACE FUNCTION FLAG_CONSECUTIVE_ROW(p_ROOM_CODE VARCHAR2,p_ENV_TEST_TYPE_CODE varchar2,p_ENV_TEST_SUBTYPE_CODE varchar2,
p_ENV_TEST_COND_CODE varchar2,p_DYNAMIC_ACTION_CODE varchar2,p_DYNAMIC_TYPE_CODE varchar2,
p_LOT_NUM varchar2,p_FORMULATION_NUM varchar2,p_TEST_DATE date,p_no_of_occur number,p_greater_than_val number,
p_time_diff number)
RETURN varchar2 IS
p_prev_rec_time date;
p_cur_rec_time date;
p_next_rec_time date;
p_v1 number;
p_v2 number;
p_v3 number;
p_v4 number;
p_v5 number;
p_v6 number;
p_v7 number;
p_v8 number;
p_v9 number;
Type num_array_type is table of number(36,6)index by binary_integer;
v_num_array num_array_type;
BEGIN
p_cur_rec_time :=p_TEST_DATE;
select p_cur_rec_time-(p_time_diff/(24*60)) into p_prev_rec_time from dual;
select p_cur_rec_time+(p_time_diff/(24*60)) into p_next_rec_time from dual;
Begin
select PART_50_CNT_1,PART_50_CNT_2,PART_50_CNT_3 into p_v1,p_v2,p_v3
from ROOM_TEST_TRANS
where ROOM_CODE =p_ROOM_CODE
and ENV_TEST_TYPE_CODE =p_ENV_TEST_TYPE_CODE
and ENV_TEST_SUBTYPE_CODE =p_ENV_TEST_SUBTYPE_CODE
and ENV_TEST_COND_CODE =p_ENV_TEST_COND_CODE
and DYNAMIC_ACTION_CODE =p_DYNAMIC_ACTION_CODE
and DYNAMIC_TYPE_CODE =p_DYNAMIC_TYPE_CODE
and LOT_NUM =p_LOT_NUM
and FORMULATION_NUM =p_FORMULATION_NUM
and TEST_DATE =p_prev_rec_time;
Exception
when no_data_found then
null;
End;
select PART_50_CNT_1,PART_50_CNT_2,PART_50_CNT_3 into p_v4,p_v5,p_v6
from ROOM_TEST_TRANS
where ROOM_CODE =p_ROOM_CODE
and ENV_TEST_TYPE_CODE =p_ENV_TEST_TYPE_CODE
and ENV_TEST_SUBTYPE_CODE =p_ENV_TEST_SUBTYPE_CODE
and ENV_TEST_COND_CODE =p_ENV_TEST_COND_CODE
and DYNAMIC_ACTION_CODE =p_DYNAMIC_ACTION_CODE
and DYNAMIC_TYPE_CODE =p_DYNAMIC_TYPE_CODE
and LOT_NUM =p_LOT_NUM
and FORMULATION_NUM =p_FORMULATION_NUM
and TEST_DATE =p_cur_rec_time;
Begin
select PART_50_CNT_1,PART_50_CNT_2,PART_50_CNT_3 into p_v7,p_v8,p_v9
from ROOM_TEST_TRANS
where ROOM_CODE =p_ROOM_CODE
and ENV_TEST_TYPE_CODE =p_ENV_TEST_TYPE_CODE
and ENV_TEST_SUBTYPE_CODE =p_ENV_TEST_SUBTYPE_CODE
and ENV_TEST_COND_CODE =p_ENV_TEST_COND_CODE
and DYNAMIC_ACTION_CODE =p_DYNAMIC_ACTION_CODE
and DYNAMIC_TYPE_CODE =p_DYNAMIC_TYPE_CODE
and LOT_NUM =p_LOT_NUM
and FORMULATION_NUM =p_FORMULATION_NUM
and TEST_DATE =p_next_rec_time;
Exception
when no_data_found then
null;
End;
IF p_v1 is not null and p_v1>p_greater_than_val then v_num_array( 1):=0; else v_num_array(1):=null; end if;
IF p_v2 is not null and p_v2>p_greater_than_val then v_num_array( 2):=0; else v_num_array(2):=null; end if;
IF p_v3 is not null and p_v3>p_greater_than_val then v_num_array( 3):=0; else v_num_array(3):=null; end if;
IF p_v4 is not null and p_v4>p_greater_than_val then v_num_array( 4):=0; else v_num_array(4):=null; end if;
IF p_v5 is not null and p_v5>p_greater_than_val then v_num_array( 5):=0; else v_num_array(5):=null; end if;
IF p_v6 is not null and p_v6>p_greater_than_val then v_num_array( 6):=0; else v_num_array(6):=null; end if;
IF p_v7 is not null and p_v7>p_greater_than_val then v_num_array( 7):=0; else v_num_array(7):=null; end if;
IF p_v8 is not null and p_v8>p_greater_than_val then v_num_array( 8):=0; else v_num_array(8):=null; end if;
IF p_v9 is not null and p_v9>p_greater_than_val then v_num_array( 9):=0; else v_num_array(9):=null; end if;
If v_num_array(1)=v_num_array(2) and v_num_array(2)=v_num_array(3) and v_num_array(3)=v_num_array(4) then return 'Y'; end if;
If v_num_array(2)=v_num_array(3) and v_num_array(3)=v_num_array(4) and v_num_array(4)=v_num_array(5) then return 'Y'; end if;
If v_num_array(3)=v_num_array(4) and v_num_array(4)=v_num_array(5) and v_num_array(5)=v_num_array(6) then return 'Y'; end if;
If v_num_array(4)=v_num_array(5) and v_num_array(5)=v_num_array(6) and v_num_array(6)=v_num_array(7) then return 'Y'; end if;
If v_num_array(5)=v_num_array(6) and v_num_array(6)=v_num_array(7) and v_num_array(7)=v_num_array(8) then return 'Y'; end if;
If v_num_array(6)=v_num_array(7) and v_num_array(7)=v_num_array(8) and v_num_array(8)=v_num_array(9) then return 'Y'; end if;
return 'N';
END;
/
This function will flag the row where atleast one column is part of morethan 3 consecutive cycle.

Here is the view definition,
CREATE OR REPLACE VIEW ROOM_TEST_TRANS_N_CONSEC_VIEW
(ROOM_TEST_TRANS_KEY, FLAG_YES_NO)
AS
select ROOM_TEST_TRANS_KEY , FLAG_CONSECUTIVE_ROW(ROOM_CODE,ENV_TEST_TYPE_CODE,ENV_TEST_SUBTYPE_CODE,
ENV_TEST_COND_CODE,DYNAMIC_ACTION_CODE,DYNAMIC_TYPE_CODE,LOT_NUM,FORMULATION_NUM,TEST_DATE,
3,1,4) flag_yes_no from ROOM_TEST_TRANS
where part_50_cnt_1 is not null
or part_50_cnt_2 is not null
or part_50_cnt_3 is not null
/


My sincere thanks to everyone.

Thanks,
Rajan