0

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
Manish K Singh
  • 35
  • 7
  • That's great. What have you attempted? – OldProgrammer May 14 '20 at 19:34
  • I want result by use of oracle stored procedure but not able to get it – Manish K Singh May 14 '20 at 19:36
  • Why "by use of oracle stored procedure"? The natural way is "by use of standard SQL query". Separate question: what is your Oracle database version (as in, for example, 11.2.0.4 or 12.1.0.2 etc.)? If you don't know, run `select banner from v$version` and see what it says. –  May 14 '20 at 19:39

1 Answers1

1

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