morethan 3 consecutive rows

[rajan1]

We have to find and flag the occurance of values >=3 if it happens more than 3 consecutive cycles/rows.It could happen within same subtype or different subtypes

example:-

time subtype value flag
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3 Y
7:06 a 3 Y
7:06 a 4 Y
7:09 b 5 Y
7:09 b 2
7:09 b 1

[DaPi]

Is this it?
select myTime from myTable
group by myTime
having count(*) >= 3

[DaPi]

or maybe this is what you want:select * from (
select myTime, subtype, value, flag,
count(*) over (partition by myTime) cntr
from myTable
) where cntr >= 3

[cjard]

sounds more complicated.. i think it will be a sliding window, ordered by time, looking back over 4 rows, to see if the sum value over the 4 rows is >= 12 and the min value for the 4 rows is >= 3


thing is.. dude hasnt said which rows he wants to flag

[rajan1]

Thanks a lot for the response.

Sorry,my bad. This is how the data looks like in the table case_study

time subtype value
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3
7:06 a 3
7:06 a 4
7:09 b 5
7:09 b 2
7:09 b 1

In this we need to find value >=3 when it occurs three or more than three consecutive times/rows. In the above example the bold rows have values in the value column >=3 and it occurs more than 3 times consecutively. We have to flag or alert such rows.

[DaPi]

Am I right in thinking that the table doesn't have a primary key?

If I'm right, this is going to be painful.

P.S. I might have spotted that "value" was a column name if you'd formated your post with code tags (the # icon produces [ code ] and [ / code ])

[rajan1]

Yes, there is no primary key.

Here is one SQL (in bold) i found while googling, here the author compare the values between consecutive rows and gets the difference between two consecutive row values.I am not able to apply this to my requirement.

I want to compare consecutive rows in a table, e.g., 1-2, 2-3, etc. Now I was thinking of doing a self join for this. But the problem is, how do I do this, as none of the columns are predictable. For example, if I have the following data:

name quantity cost
A 2 20
H 7 24
L 1 12
...
and I want to find out the difference between the cost of A and H, H and L, and so on.

This question posed on 06 January 2006

Interesting question. We need to join each row to the row which has the lowest name of all the names that are greater than the name in the row being joined. Simple, yes? I mean, once you say it like that, it's easy to understand, right?

Well, once you say it like that, it's easy to write the SQL for, too.

select t1.name
, t1.cost as t1_cost
, t2.cost as t2_cost
, t2.cost - t1.cost as diff
from yourtable as t1
left outer
join yourtable as t2
on t2.name
= ( select min(name)
from yourtable
where name > t1.name )

name t1_cost t2_cost diff
A 20 24 4
H 24 12 -12
L 12 -- --
It's an unusual ON condition, because you don't see one like this every day. It may be rare but it is perfectly valid.

Thanks for the tips.

[tamilselvan]

Why don't you use simple "lead" analytical fn?


SQL> select * from int;

NAME QTY COST
---------- ---------- ----------
A 2 20
H 7 24
L 1 12

SQL> get int2
1 select name, qty, cost,
2 lead(cost) over ( order by name) nextcost
3* from int
SQL> /

NAME QTY COST NEXTCOST
---------- ---------- ---------- ----------
A 2 20 24
H 7 24 12
L 1 12



Tamil

[rajan1]

Thanks a lot for your response.

I really don't want the difference between consecutive rows. What i really want is the occurance of more than 3 consecutive rows having value>=3

time subtype value
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3
7:06 a 3
7:06 a 4
7:09 b 5
7:09 b 2
7:09 b 1

In the above example the bold rows have values in the value column >=3 and it occurs more than 3 times consecutively. We have to flag or alert such rows.

[tamilselvan]

You said the table does not have PK.

Then what is the sorting order to determine 3 consecutive rows that have value >= 3?

Tamil

[rajan1]

Thanks a lot. I was about to post the sorting order

Order by date,time

Sorry, i did not included Date as a column in my example above.

[tamilselvan]

Is it a single date column?
Or
You store the time separately?

Tamil

[rajan1]

The table has,

Date,Time,SubType,Value as columns

3/1/2006,7:00a.m,a,3
3/1/2006,7:00a.m,a,1
3/1/2006,7:00a.m,a,1
3/1/2006,7:03a.m,a,3
3/1/2006,7:03a.m,a,4
3/1/2006,7:03a.m,a,5
3/1/2006,7:06a.m,a,6
3/1/2006,7:06a.m,a,1
3/1/2006,7:06a.m,a,2
3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,4
3/1/2006,7:12a.m,b,3
3/1/2006,7:12a.m,b,2
3/1/2006,7:12a.m,b,1

[DaPi]

Sorting by date/time (why don't you use an Oracle DATE, which includes both?) will not be sufficient to get deterministic results. As you can see by the following possible retrieval orders:
3/1/2006,7:09a.m,a,1
3/1/2006,7:09a.m,a,2
3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,4
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,6
3/1/2006,7:09a.m,a,7
3/1/2006,7:09a.m,a,8
3/1/2006,7:09a.m,a,9

and

3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,4
3/1/2006,7:09a.m,a,1
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,6
3/1/2006,7:09a.m,a,2
3/1/2006,7:09a.m,a,7
3/1/2006,7:09a.m,a,8
3/1/2006,7:09a.m,a,9

[jmodic]

Here is one of the possible solutions.

(Note that for the simplicity of this case I deliberately made the data in the TIME column unique, so that we don't have a unique sorting problem that DaPi is refering to in the previous post.)

SQL> select * from tab1 order by time;

TIME SUBTYPE VALUE
-------- ------- ----------
07:00:00 a 2
07:00:20 a 0
07:00:40 a 5
07:03:00 a 3
07:03:20 a 2
07:03:40 a 1
07:06:00 a 3
07:06:20 a 3
07:06:40 a 4
07:09:00 b 5
07:09:20 b 2
07:09:40 b 1

12 rows selected.

SQL> select
2 time, subtype, value,
3 case when preceding = 3 or following = 3 then 'y' end as flag
4 from (select
5 time, subtype, value, indicator3, rownum,
6 count(indicator3) over(order by rownum rows between 2 preceding and current row) as preceding,
7 count(indicator3) over(order by rownum rows between current row and 2 following) as following
8 from (select
9 time, subtype, value,
10 case when value >= 3 then 1 end as indicator3
11 from tab1
12 order by time
13 )
14 );

TIME SUBTYPE VALUE FLAG
-------- ------- ---------- ----
07:00:00 a 2
07:00:20 a 0
07:00:40 a 5
07:03:00 a 3
07:03:20 a 2
07:03:40 a 1
07:06:00 a 3 y
07:06:20 a 3 y
07:06:40 a 4 y
07:09:00 b 5 y
07:09:20 b 2
07:09:40 b 1

12 rows selected.

SQL>

[tamilselvan]

Thanks Modic.

Even the time column is not unique, I can generate artifical unique key using rownum.

Below is the SQL that did not use analytical function.



SQL> select * from test ;

RTIME T VALUE
---------- - ----------
7.00 a 2
7.00 a 0
7.00 a 5
7.03 a 2
7.06 a 3
7.06 a 3
7.06 a 4
7.09 b 5
7.09 b 2
7.09 b 1

10 rows selected.

select distinct *
from (
select *
from ( select rn, rtime, least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn +1 = iv1.rn
)
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn +2 = iv1.rn
)
union all
select *
from ( select rn, rtime, least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn -1 = iv1.rn
)
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn +1 = iv1.rn
)
union all
select *
from ( select rn, rtime, least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn -1 = iv1.rn
)
and iv1.leastval = ( select leastval
from ( select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn -2 = iv1.rn
)
)
/

RN RTIME LEASTVAL
---------- ---------- ----------
5 7.06 3
6 7.06 3
7 7.06 3
8 7.09 3



I will rewrite it using analytical function.

Tamil

[DaPi]

Even the time column is not unique, I can generate artifical unique key using rownum.
Hi Tamil,

That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time. I suspect the OP will have to introduce a sequence number at the point at which the rows are inserted in the table, to have a bomb-proof solution.

If an update is required (as I suspect) then it could be done via ROWID:
Update myTable Set flag='Y'
Where rowid in
(a version of Juri's select statement, including rowid)

[tamilselvan]

That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time.


I thought about that. That's why I asked the original poster about the sorting order.
We can assign the rownum only on the sorted result set. In this case we can get what we want.

Using rowid for update. You are right. If we want to update another column in the table, then we must use rowid for update.

Tamil

[rajan1]

Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.

[tamilselvan]

Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.

You are right. Then add date col in the order by clause.

OR

Even after you combine those 2 columns, they will not form a unique key. I suggest you should add a running seq col into the table, and make it as PK.

Tamil

[cjard]

can you not just sort by ROWID to get the natural order that rows were inserted into the table? is it valid?

[cjard]

ok i finally whacked this one but for one little bug:



create table tmp_test as
select '3/1/2006 7:00a.m' as d,'a' as t,'3' as v from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:03a.m','a','3' from dual union all
select '3/1/2006 7:03a.m','a','4' from dual union all
select '3/1/2006 7:03a.m','a','5' from dual union all
select '3/1/2006 7:06a.m','a','6' from dual union all
select '3/1/2006 7:06a.m','a','1' from dual union all
select '3/1/2006 7:06a.m','a','2' from dual union all
select '3/1/2006 7:09a.m','a','3' from dual union all
select '3/1/2006 7:09a.m','a','5' from dual union all
select '3/1/2006 7:09a.m','a','4' from dual union all
select '3/1/2006 7:12a.m','b','3' from dual union all
select '3/1/2006 7:12a.m','b','2' from dual union all
select '3/1/2006 7:12a.m','b','1' from dual;


following my own advice (in english) given last post i made this query:


select
d,
t,
v,
case when
SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 and
MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
1
end as consec
from tmp_test


it reliably identifies consecutives and for run length of N, the last N-2 rows are flagged with a 1. i called this column CONSEC:

3/1/2006 7:00a.m a 1
3/1/2006 7:00a.m a 1
3/1/2006 7:03a.m a 3
3/1/2006 7:03a.m a 4
3/1/2006 7:03a.m a 5 1
3/1/2006 7:06a.m a 6 1
3/1/2006 7:06a.m a 1
3/1/2006 7:06a.m a 2


so now we can use a lookahead to see if the CONSEC column holds a 1 character, and because our CONSEC currently shows all but the first 2 of a consecutive range, we look ahead 2, or 1, or look at the current position:


select
d,
t,
v,
consec,
case when
consec = 1 or
lead(consec,1) OVER (ORDER BY d, rowid) = 1 or
lead(consec,2) OVER (ORDER BY d, rowid) = 1
then 'Y'
end as flag

from
(
select
d,
t,
v,
case when
SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 9 and
MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
1
end as consec
from tmp_test
);


results:


D T V CONSEC FLAG
3/1/2006 7:00a.m a 3
3/1/2006 7:00a.m a 1
3/1/2006 7:00a.m a 1
3/1/2006 7:03a.m a 3 Y
3/1/2006 7:03a.m a 4 Y
3/1/2006 7:03a.m a 5 1 Y
3/1/2006 7:06a.m a 6 1 Y
3/1/2006 7:06a.m a 1
3/1/2006 7:06a.m a 2
3/1/2006 7:09a.m a 3 Y
3/1/2006 7:09a.m a 5 Y
3/1/2006 7:09a.m a 4 1 Y
3/1/2006 7:12a.m b 3 1 Y
3/1/2006 7:12a.m b 2
3/1/2006 7:12a.m b 1
3/1/2006 7:13a.m c 4 Y
3/1/2006 7:14a.m d 4 Y
3/1/2006 7:14a.m d 5 1 Y
3/1/2006 7:14a.m d 1
3/1/2006 7:14a.m d 3
3/1/2006 7:14a.m d 3
3/1/2006 7:14a.m d 1


(i added some more values to the table after the initial create table sql)


stomped! :) and we have learned some interesting things about analytic functions too :)

[cjard]

please note i forgot the question and even ignored my own advice a little, i prepared an sql for more than 2 consecutive rows, and poster wanted more than 3, so heres how we change the sql:


select
d,
t,
v,
consec,
case when
consec = 1 or
lead(consec,1) OVER (ORDER BY d, rowid) = 1 or
lead(consec,2) OVER (ORDER BY d, rowid) = 1 or
--add a lead row for every blank. more than 3 consec needs 3 lead rows
--more than 5 consec (i.e. 6+ consec) needs 5 leads
--note that each lead row must lead a higher lookahead
--the lookahead of this row is 3
lead(consec,3) OVER (ORDER BY d, rowid) = 1
then 'Y'
end as flag

from
(
select
d,
t,
v,
case when
--the sum over the last N rows (more than 3 rows requires 3 PRECEDING)
--must be CONSECUTIVES_REQD * MIN_THRESHOLD
--in our case we want a run of 4 or more rows with value >= 3
--so sum must be >= 4rows*3orMore == 4*3 == 12

--remember to change the X PRECEDING value of X
SUM(v) OVER (ORDER BY d, rowid ROWS 3 PRECEDING)>= 12 and
MIN(v) OVER (ORDER BY d, rowid ROWS 3 PRECEDING)>= 3 then
1
end as consec
from tmp_test
);



so for a consecutive run length N of values greater than Y you need

N-1 entries in the LEAD() section of the sql, with lead values 1 to N-1
a SUM() over the preceding N-1 rows of value >= N*Y
a MIN() over the preceding N-1 rows of value >= Y

ok, now you can edit this sql for any run length of any value

[jmodic]

can you not just sort by ROWID to get the natural order that rows were inserted into the table? is it valid?
Of course not! ROWID has got nothing to do with what you call "natural order that rows were inserted into the table". There's no such thing in the Oracle database as built-in indicator of the inserted rows ordering. ROWID merely represents *the possition* of a particular row on the disk storage, it has no information about the time the row was inserted. And by comparing ROWIDs of two rows, you can not say which one was inserted befor the other one.

So, in short: ROWID can not be used for ordering rows in the way that original poster needs to.

[cjard]

Of course not! ROWID has got nothing to do with what you call "natural order that rows were inserted into the table". There's no such thing in the Oracle database as built-in indicator of the inserted rows ordering. ROWID merely represents *the possition* of a particular row on the disk storage, it has no information about the time the row was inserted. And by comparing ROWIDs of two rows, you can not say which one was inserted befor the other one.

So, in short: ROWID can not be used for ordering rows in the way that original poster needs to.

well, dude's going to ahve to decide on something else that is precise enough to order his rows properly then, eh? i've only ever seen rowid increment for the data work that ive been doing and its been quite a reliable thing that for two rows inserted in the same second, the rowid has been able to order them in order of creation. i'll take your advice on board though should i need it in the future. thanks!

[jmodic]

i've only ever seen rowid increment for the data work that ive been doing and its been quite a reliable thing that for two rows inserted in the same second, the rowid has been able to order them in order of creation.
Then I suppose you've never ever observed how new rows can take place that has been releeased by deleted rows from the same table, thus obtaining a "lower" ROWIDs compared to some rows that were inserted before them? Or new table extents beeng allocated in the tablespace in such location that all rows that will end in that extent in the future will result in ROWIDs that are lower than any currently existing table's row? Or that ROWIDs of the existing rows can change for various reasons, thus making the insertion time even less corelated to the row's ROWID?

In short, as I've allready said: by comparing ROWIDs of two rows, you can definitely not conclude which of those two rows was inserted before the other one. All you can do based on their ROWIDs is *to guess*.

[rajan1]

Hi all,

Finally, i got the exact requirement from the client. Please find the requirement in the attached word document.

The requirement is very scary to me. Hope the experts can help.

Thanks,
Rajan

[DaPi]

The requirements STILL don't explicitly include a sort order. Your client must specify that.

Is it by (id, testdate)?
Does this produce a UNIQUE sequence?

[rajan1]

Hi all,

I was bouncing back and forth with the client to confirm on the requirement. As a result i modified the requirement please find the modified requirement attached. I am trying my best to clearly define the requirement to our experts here.

1. Sort is by id and testdate
2. Select all the rows where the difference between (testdate column) current row and the lead row or the next row is 3 minutes.

I don't know how to proceed with consecutive times. Experts please help.

Thanks,
Rajan

[rajan1]

The three consecutive applies only within a date or for each date.

Thanks,
Rajan

[rajan1]

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