Suppose main table is
ID Usage_flah
1 null
2 null
3 null
4 Yes
5 Yes
6 Null
7 NUll
Now I want start and end position of ID in second result table where usage_flag is null
Like
Start End
1 3
6 7
Here is one way (demonstrating why knowing your Oracle version is relevant: this solution uses match_recognize, introduced in Oracle 12.1)
The with clause is only to simulate your input data; you should remove it, and use your actual table and column names in the main query (select * .......)
with
main_table (id, usage_flag) as (
select 1, null from dual union all
select 2, null from dual union all
select 3, null from dual union all
select 4, 'Yes' from dual union all
select 5, 'Yes' from dual union all
select 6, null from dual union all
select 7, null from dual
)
select *
from main_table
match_recognize (
order by id
measures first(id) as id_start, last(id) as id_end
pattern ( n+ )
define n as usage_flag is null
);
ID_START ID_END
---------- ----------
1 3
6 7