Home » SQL & PL/SQL » SQL & PL/SQL » Filling gaps from another table
Filling gaps from another table [message #421680] Wed, 09 September 2009 14:43 Go to next message
jammula
Messages: 6
Registered: September 2009
Junior Member
Thanks in advance

Could any one help me building query(i remember using connect start with)

I just need to fill the gap of record of RN record only

select * from table_aa3 where region = 1
 
A	BEG	END	PROF	AVERAGE	REGION	RN
1	0	0.1	159	159	1	1
3	0.2	0.3	179	159	1	3
4	0.1	0.2	250	300	1	4


If you see above results based on condition (RN = 2 is missing in the order)

I need to fill missing record betweem records fron above condition from table_aa2

so my final result will be
A	BEG	END	PROF	AVERAGE	REGION	RN
1	0	0.1	159	159	1	1 ---table_aa3
2	0.1	0.2	159	168	1	2 ---- Table_aa2
3	0.2	0.3	179	159	1	3 ---table_aa3
4	0.1	0.2	250	300	1	4 -- table_aa3


Second Example:
select * from table_aa3 where region = 2



A	BEG	END	PROF	AVERAGE	REGION	RN
5	0.2	0.3	320	250	2	1
7	0.2	0.3	388	379	2	3
8	0.3	0.4	379	388	2	4
9	0.4	0.5	388	400	2	5
10	1.5	0.6	499	500	2	6



If you see above result RN = 2 is missing between records 1,3,4,5,6

I need to fill missing record(RN = 2) betweem records fron above condition from table_aa2
A	BEG	END	PROF	AVERAGE	REGION	RN
5	0.2	0.3	320	250	2	1 ---table_aa3
6	0.3	0.4	250	380	2	2
7	0.2	0.3	388	379	2	3---table_aa3
8	0.3	0.4	379	388	2	4---table_aa3
9	0.4	0.5	388	400	2	5---table_aa3
10	1.5	0.6	499	500	2	6---table_aa3


create table TABLE_AA2
(
A NUMBER,
BEG NUMBER,
END NUMBER,
PROF NUMBER,
AVERAGE NUMBER,
REGION NUMBER,
RN NUMBER
)
;

prompt Loading TABLE_AA2...
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (1, 0, .1, 159, 159, 1, 1);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (2, .1, .2, 159, 168, 1, 2);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (3, .2, .3, 179, 159, 1, 3);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (4, .1, .2, 250, 300, 1, 4);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (5, .2, .3, 320, 250, 2, 1);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (6, .3, .4, 250, 380, 2, 2);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (7, .2, .3, 388, 379, 2, 3);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (8, .3, .4, 379, 388, 2, 4);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (9, .4, .5, 388, 400, 2, 5);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (10, 1.5, .6, 499, 500, 2, 6);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (11, .5, .6, 420, 448, 3, 1);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (12, .6, .7, 520, 530, 3, 2);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (13, .7, .8, 540, 550, 3, 3);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (14, .9, 1, 560, 570, 3, 4);
commit;


create table TABLE_AA3
(
A NUMBER,
BEG NUMBER,
END NUMBER,
PROF NUMBER,
AVERAGE NUMBER,
REGION NUMBER,
RN NUMBER
)
;

prompt Loading TABLE_AA3...
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (1, 0, .1, 159, 159, 1, 1);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (3, .2, .3, 179, 159, 1, 3);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (4, .1, .2, 250, 300, 1, 4);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (5, .2, .3, 320, 250, 2, 1);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (7, .2, .3, 388, 379, 2, 3);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (8, .3, .4, 379, 388, 2, 4);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (9, .4, .5, 388, 400, 2, 5);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (10, 1.5, .6, 499, 500, 2, 6);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (11, .5, .6, 420, 448, 3, 1);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (12, .6, .7, 520, 530, 3, 2);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (13, .7, .8, 540, 550, 3, 3);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (14, .9, 1, 560, 570, 3, 4);
commit;

[Updated on: Wed, 09 September 2009 15:08]

Report message to a moderator

Re: Filling gaps from another table [message #421682 is a reply to message #421680] Wed, 09 September 2009 15:57 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Why not you select the data from Table_aa2 itself?
Re: Filling gaps from another table [message #421740 is a reply to message #421680] Thu, 10 September 2009 03:57 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
SELECT * FROM 
(
  SELECT * FROM table_aa3
  UNION all
  SELECT * FROM table_aa2
  WHERE (region,rn) IN
  (
    select region,rn from table_aa2
    minus
    select region,rn from table_aa3
  )
)
ORDER BY region,rn;
Re: Filling gaps from another table [message #421795 is a reply to message #421682] Thu, 10 September 2009 10:59 Go to previous messageGo to next message
jammula
Messages: 6
Registered: September 2009
Junior Member
Thanks for your help

Following example I want to display only RN =2 from TABLE_AA2

My requirement is i need to get the missing gap from the list of query from Table_aa2


I need to look the min and max of RN for Region and Fill the missing gap record for that Region

For example


SELECT * FROM table_aa3 where region in( 1,2) and a in(1,3,4,5)



A	BEG	END	PROF	AVERAGE	REGION	RN
1	0	0.1	159	159	1	1
3	0.2	0.3	179	159	1	3
4	0.1	0.2	250	300	1	4
5	0.2	0.3	320	250	2	1


In the above case i need to get only RN =2 from table_aa2

already i have For Region = 1 (1,3,4 &)
Region 2 = 2 (1)


When i ran the query i am getting the RN = 2 and RN = 6 from table_aa2

SELECT * FROM 
(
  SELECT * FROM table_aa3 where region in( 1,2) and a in(1,3,4,5)
  UNION all
  SELECT * FROM table_aa2
  WHERE (region,rn) IN
  (
    select region,rn from table_aa2 where region in(1,2)
    minus
    select region,rn from table_aa3 where region in(1,2)
  )
)
ORDER BY region,rn;



   	A	BEG	END	PROF	AVERAGE	REGION	RN
1	1	0	0.1	159	159	1	1
2	2	0.1	0.2	159	168	1	2
3	3	0.2	0.3	179	159	1	3
4	4	0.1	0.2	250	300	1	4
5	5	0.2	0.3	320	250	2	1
6	6	0.3	0.4	250	380	2	2


Re: Filling gaps from another table [message #421894 is a reply to message #421795] Fri, 11 September 2009 08:17 Go to previous message
jammula
Messages: 6
Registered: September 2009
Junior Member
can anybody help please
Previous Topic: additional condition
Next Topic: Convert number to date using numtodsinterval and timestamp
Goto Forum:
  


Current Time: Wed Sep 28 21:12:44 CDT 2016

Total time taken to generate the page: 0.06027 seconds