| Insertion logic [message #623133] |
Fri, 05 September 2014 00:43  |
 |
shumail
Messages: 149
Registered: September 2012
Location: Canada
|
Senior Member |
|
|
Hi All
I would really appreciate if someone help me out and solve my issue.I have the following data.
Data Sample
Drop and delete table
Drop table parent_mst;
drop table parent_dtl;
drop table p1;
drop table p2;
drop SEQUENCE p_seq;
----------------
delete from parent_mst;
delete from parent_dtl;
delete from p1;
delete from p2;
Tables and Sequence Creation
CREATE SEQUENCE p_seq
START WITH 1
INCREMENT BY 1;
create table p1
(
grp number(3),
nel number(4)
);
---------
create table p2
(
grp number(3),
per number(4)
);
create table parent_mst
(
grp number(3),
nel number(4),
ppk_seq number(5)
);
create table parent_dtl
(
grp number(3),
per number(4),
ppk_seq number(5)
);
Insertion
insert into p1
Select 100 grp,8000 nel from dual
union all
Select 100 grp,9000 nel from dual
union all
Select 200 grp,1000 nel from dual
union all
Select 300 grp,1000 nel from dual
;
insert into p2
select 100 , 200 from dual union all
select 100 , 300 from dual union all
select 100 , 500 from dual union all
select 200 ,400 from dual union all
select 200 ,600 from dual
;
Query on p1
select * from p1;
[b]Query Result[/b]
[b]GRP, NEL[/b]
100 8000
200 1000
300 1000
100 9000
Query on p2
Select * from p2;
[b]Query Result[/b]
[b]GRP PER[/b]
100 200
100 300
100 500
200 400
200 600
500 600
My requirement is that I need to insert records in parent_mst and parent_dtl by using data of p1 and p2 tables.
the way of populating data in tables are:
Rules
--System get all unique PER against GRP from p2 table for example for grp=100 we have PER like 200,300,500
-- Go to p1 table and match grp and if exist then create 1 record in parent_mst table against grp and NEL (unique combination) and get its column ppk_seq and insert that ppk_seq with set of data.
Required Result
Data should be inserted in parent_mst table
GRP NEL ppk_seq
100 8000 1
100 9000 2
200 1000 3
Data should be inserted in parent_dtl table
GRP PER ppk_seq
100 200 1
100 300 1
100 500 1
100 200 2
100 300 2
100 500 2
200 400 3
200 600 3
Please let me know if you have any question, Sorry about my poor English. Thanks
Lalit : Added code tags.
In future please do it yourself, read How to use [code] tags
[Updated on: Fri, 05 September 2014 02:31] by Moderator Report message to a moderator |
|
|
|
| Re: Insertion logic [message #623135 is a reply to message #623133] |
Fri, 05 September 2014 01:15  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> insert into parent_mst (grp, nel, ppk_seq)
2 select grp, nel, p_seq.nextval
3 from p1
4 where exists (select null from p2 where p2.grp=p1.grp)
5 /
3 rows created.
SQL> insert into parent_dtl
2 select p.grp, p2.per, p.ppk_seq
3 from parent_mst p, p2
4 where p2.grp = p.grp
5 /
8 rows created.
SQL> select * from parent_mst order by ppk_seq;
GRP NEL PPK_SEQ
---------- ---------- ----------
100 9000 1
100 8000 2
200 1000 3
3 rows selected.
SQL> select * from parent_dtl order by ppk_seq, grp, per;
GRP PER PPK_SEQ
---------- ---------- ----------
100 200 1
100 300 1
100 500 1
100 200 2
100 300 2
100 500 2
200 400 3
200 600 3
8 rows selected.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
