Oracle SQL to Find missing number in given range of values

 

 

 Expected Output:

 

 

DROP TABLE A;        

CREATE TABLE A (NAME VARCHAR2(10 BYTE), S_START NUMBER, S_END NUMBER);       

Insert into A (NAME,S_START,S_END) values ('serial 1',1,10);         

Insert into A (NAME,S_START,S_END) values ('serial 2',15,20);        

         

DROP TABLE B         

  CREATE TABLE B         

   (    NAME VARCHAR2(10 BYTE),          

    S_VAL NUMBER         

   );        

Insert into DILEEP.B (NAME,S_VAL) values ('serial 1',1);         

Insert into DILEEP.B (NAME,S_VAL) values ('serial 1',2);         

Insert into DILEEP.B (NAME,S_VAL) values ('serial 1',3);         

Insert into DILEEP.B (NAME,S_VAL) values ('serial 1',6);         

Insert into DILEEP.B (NAME,S_VAL) values ('serial 1',8);         

Insert into DILEEP.B (NAME,S_VAL) values ('serial 1',10);        

Insert into DILEEP.B (NAME,S_VAL) values ('serial 2',16);        

Insert into DILEEP.B (NAME,S_VAL) values ('serial 2',17);        

         

select * from a,

lateral(select level l from dual connect by level<=a.S_END-a.S_START);

select name,s_start+l from a,

lateral(select level l from dual connect by level<=a.S_END-a.S_START+1);

select name,s_start+l-1 from a,

lateral(select level l from dual connect by level<=a.S_END-a.S_START+1);

select name,s_start+l-1 from a,

lateral(select level l from dual connect by level<=a.S_END-a.S_START+1)

minus

select * from b;