CODEWITHSUNDEEP
sqloracle-databaseoracle11gnull
Sai
Sai

Reputation: 689

How to get four continuous nulls in a column

Having:

create table theater(seatno varchar2(3),status varchar(1));

insert into theater('A1','');
insert into theater('A2','B');
insert into theater('A3','B');
insert into theater('A4','');
insert into theater('A5','');
insert into theater('A6','');
insert into theater('A7','');

My requirement is to get four continuous seat numbers where status is null
A4,A5,A6,A7

I have tried Lead,Lag functions with no success.

Upvotes: 1

Views: 121

Answers (3)

Patrick Bacon
Patrick Bacon

Reputation: 4640

I would use derived tables to add a row_seatno and a col_seatno columns and then use the listagg function to create aggregated list of the status of the entire row.

Next I would find instances of 4 NULLS consecutively which would have this substring, 'N;N;N;N', as follows:

        select seatno
  ||', '
  || mod3_t.row_seatno
  || to_char( to_number(mod3_t.col_seatno) + 1)
  || ', '
  || mod3_t.row_seatno
  || to_char( to_number(mod3_t.col_seatno) + 2)
  || ', '
  || mod3_t.row_seatno
  || to_char(to_number(mod3_t.col_seatno) + 3)
from
  (select mod2_t.seatno,
    mod2_t.row_seatno,
    mod2_t.col_seatno,
    instr(mod2_t.row_status, 'N;N;N;N', to_number(mod2_t.col_seatno) ,1) cont_null
  from
    (select mod1_t.seatno,
      mod1_t.row_seatno,
      mod1_t.col_seatno,
      listagg(nvl(mod1_t.status,'N'), ';') within group (
    order by mod1_t.col_seatno) over (partition by mod1_t.row_seatno ) row_status
    from
      (select regexp_substr( t.seatno, '[[:upper:]]+',1,1) row_seatno,
        regexp_substr( t.seatno, '[[:digit:]]+',1,1) col_seatno,
        seatno,
        status
      from theater t
      ) mod1_t
    ) mod2_t
  where cont_null =1
  ) mod3_t

Upvotes: -1

vav
vav

Reputation: 4694

first step, get only null in status (trivial):

select * from  theater
where status is null

now we will group seats into continous groups:

select seat - row_number() over (order by seat) grp, seat
where status is null

this will give you same group for the continous group of seats, like this

grp   seat
---   ----
0     1
0     2
1     4
1     5

then within a group you ask for next 4th element

select lead(seat, 4) over (partition by grp), grp, seat from (
previous query
)

And as a final step, you filter that 4th element is not null. That will be your answer.

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

Something like this should help.

select t1.seatno
, t2.seatno
, t3.seatno
, t4.seatno

from theatre t1 join theatre t2 on t2.seatno = t1.seatno + 1
join theatre t3 on t3.seatno = t2.seatno + 1
join theatre t4 on t4.seatno = t3.seatno + 1

where t1.status is null
and t2.status is null
and t3.status is null
and t4.status is null

Upvotes: 0

Related Questions