Home » SQL & PL/SQL » SQL & PL/SQL » Master Detail View
Master Detail View [message #257730] Thu, 09 August 2007 01:46 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts ,

I want to create one view with 4 table, 2 are master and 2 are details.
trans_mst1, trans_mst2, trans_dtl1,trans_dtl2
master files have 3 feilds
doc_type , trans_no, trans_date
detail files have 3 feilds
trans_no, item_Cd, item_Qty

data in trans_mst1 is like
recv, 0001, 01-01-2007
recv, 0002, 02-01-2007
recv, 0003, 03-01-2007

data in trans_dtl1 is like
0001, 1010, 10
0001, 1011, 20
0001, 1012, 40

0002, 1010, 70
0002, 1011, 20
0002, 1012, 80

0003, 1010, 50
0003, 1011, 30
0003, 1012, 70

data in trans_mst2 is like
issu, 0001, 01-01-2007
issu, 0002, 02-01-2007
issu, 0003, 03-01-2007

data in trans_dtl2 is like
0001, 1010, 5
0001, 1011, 10
0001, 1012, 10

0002, 1010, 10
0002, 1011, 10
0002, 1012, 10

0003, 1010, 10
0003, 1011, 10
0003, 1012, 10

now i would like to merge all the tables and want to get data like this

0001, recv, 01-01-2007, 1010, 10
0001, recv, 01-01-2007, 1011, 20
0001, recv, 01-01-2007, 1012, 40
0002, recv, 02-01-2007, 1010, 70
0002, recv, 02-01-2007, 1011, 20
0002, recv, 02-01-2007, 1012, 80
0003, recv, 03-01-2007, 1010, 50
0003, recv, 03-01-2007, 1011, 30
0003, recv, 03-01-2007, 1012, 70
0001, issu, 01-01-2007, 1010, 5
0001, issu, 01-01-2007, 1011, 10
0001, issu, 01-01-2007, 1012, 10
0002, issu, 02-01-2007, 1010, 10
0002, issu, 02-01-2007, 1011, 10
0002, issu, 02-01-2007, 1012, 10
0003, issu, 03-01-2007, 1010, 10
0003, issu, 03-01-2007, 1011, 10
0003, issu, 03-01-2007, 1012, 10

any help and suggussions.


Re: Master Detail View [message #257745 is a reply to message #257730] Thu, 09 August 2007 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I suggest:
Read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Always post your Oracle version (4 decimals).
Describe with words and not ONLY ouput what should be the result.

Regards
Michel

Re: Master Detail View [message #257938 is a reply to message #257745] Thu, 09 August 2007 11:39 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
SQL> desc m1
Name Null? Type
----------------------------------------- -------- ----------------------------
OPER VARCHAR2(10)
OPER_ID NUMBER
OPER_DT DATE

SQL> desc d1
Name Null? Type
----------------------------------------- -------- ----------------------------
OPER_ID NUMBER
PART_NU NUMBER
DEPT_NU NUMBER

SQL> select * from m1;

OPER OPER_ID OPER_DT
---------- ---------- ---------
recv 1 01-JAN-07
recv 2 02-JAN-07
recv 3 03-JAN-07

SQL> select * from d1;

OPER_ID PART_NU DEPT_NU
---------- ---------- ----------
1 1010 10
1 1011 20
1 1012 40
2 1010 70
2 1011 20
2 1012 80
3 1010 50
3 1011 30
3 1012 70

9 rows selected.

SQL> select a.oper_id, a.oper, a.oper_dt, b.part_nu, b.dept_nu
2 from m1 a, d1 b
3 where a.oper_id = b.oper_id
4 order by a.oper_id;

OPER_ID OPER OPER_DT PART_NU DEPT_NU
---------- ---------- --------- ---------- ----------
1 recv 01-JAN-07 1010 10
1 recv 01-JAN-07 1011 20
1 recv 01-JAN-07 1012 40
2 recv 02-JAN-07 1010 70
2 recv 02-JAN-07 1011 20
2 recv 02-JAN-07 1012 80
3 recv 03-JAN-07 1010 50
3 recv 03-JAN-07 1011 30
3 recv 03-JAN-07 1012 70

9 rows selected.

Habeeb
Re: Master Detail View [message #257943 is a reply to message #257730] Thu, 09 August 2007 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Too bad for you that you did NOT follow Michel's advice about formatting your posts.

You're On Your Own (YOYO)!
Re: Master Detail View [message #257955 is a reply to message #257938] Thu, 09 August 2007 12:18 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
There is nothing wrong in formatting, it is a good practice, but concentrate more on providing help.
You are an IT professional not a "Clerk".

Habeeb
Re: Master Detail View [message #257956 is a reply to message #257730] Thu, 09 August 2007 12:19 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If you are so concerned about helping, post his solution trying to wade through the unformatted code.
Re: Master Detail View [message #257963 is a reply to message #257955] Thu, 09 August 2007 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Formatting help us help you.
It also shows that you have some respect for those who help you as you show that you want to make their work easier.

On the opposite, not formatting, not reading the stickies/rules, even more when you are asked to do it, show that you scorn others and consider them as your servants.

Choose your way.

Regards
Michel

[Updated on: Thu, 09 August 2007 12:30]

Report message to a moderator

Re: Master Detail View [message #257967 is a reply to message #257730] Thu, 09 August 2007 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Habeeb
>concentrate more on providing help.
Nobody here is REQUIRED to answer your questions or solve your problems.
Since you REFUSE to make it easy to read & respond to your pleas for assistance, I refuse to assist.

YOYO!
Re: Master Detail View [message #257970 is a reply to message #257967] Thu, 09 August 2007 12:48 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
I am Sorry, I did not request for any help on the contrary I did provide a solution for the orignal request.

Habeeb

Re: Master Detail View [message #257973 is a reply to message #257970] Thu, 09 August 2007 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you didn't explain what you wanted, the solution is useless for others.
I bet we can get the same output with many different ways which give different results on other data.

Hope you had fun to post it.

Regards
Michel
Re: Master Detail View [message #258317 is a reply to message #257938] Sat, 11 August 2007 00:29 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi habib

Join single master and detail is not a problem. if you see my question, i m asking about 4 tables .. there are two masters and two details. master table have same structure and detail table have same structure. Only diffrence is in data.. one master detail gives you 'RECV' and other one gives you 'ISSU' and my question is get result like this,


0001, recv, 01-01-2007, 1010, 10
0001, recv, 01-01-2007, 1011, 20
0001, recv, 01-01-2007, 1012, 40
0002, recv, 02-01-2007, 1010, 70
0002, recv, 02-01-2007, 1011, 20
0002, recv, 02-01-2007, 1012, 80
0003, recv, 03-01-2007, 1010, 50
0003, recv, 03-01-2007, 1011, 30
0003, recv, 03-01-2007, 1012, 70
0001, issu, 01-01-2007, 1010, 5
0001, issu, 01-01-2007, 1011, 10
0001, issu, 01-01-2007, 1012, 10
0002, issu, 02-01-2007, 1010, 10
0002, issu, 02-01-2007, 1011, 10
0002, issu, 02-01-2007, 1012, 10
0003, issu, 03-01-2007, 1010, 10
0003, issu, 03-01-2007, 1011, 10
0003, issu, 03-01-2007, 1012, 10


any suggusions
Re: Master Detail View [message #258565 is a reply to message #258317] Mon, 13 August 2007 01:36 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
annu-agi wrote on Sat, 11 August 2007 07:29
any suggusions
UNION ALL to the rescue!

Have a look at my test script. It is Based on the data in your opening post. It is possible that the data types do not correspond to the data types of your columns but that information was missing.

--master files have 3 fields
CREATE TABLE trans_mst1( doc_type VARCHAR2(10)
                       , trans_no NUMBER
                       , trans_dt DATE
                       )
/

CREATE TABLE trans_mst2( doc_type VARCHAR2(10)
                       , trans_no NUMBER
                       , trans_dt DATE
                       )
/

--detail files have 3 fields
CREATE TABLE trans_dtl1( trans_no NUMBER
                       , item_Cd  NUMBER
                       , item_Qty NUMBER
                       )
/

CREATE TABLE trans_dtl2( trans_no NUMBER
                       , item_Cd  NUMBER
                       , item_Qty NUMBER
                       )
/


--data in trans_mst1 is like
INSERT INTO trans_mst1 VALUES ('recv', 0001, TO_DATE('01-01-2007', 'dd-mm-yyyy'));
INSERT INTO trans_mst1 VALUES ('recv', 0002, TO_DATE('02-01-2007', 'dd-mm-yyyy'));
INSERT INTO trans_mst1 VALUES ('recv', 0003, TO_DATE('03-01-2007', 'dd-mm-yyyy'));

--data in trans_dtl1 is like
INSERT INTO trans_dtl1 VALUES (0001, 1010, 10);
INSERT INTO trans_dtl1 VALUES (0001, 1011, 20);
INSERT INTO trans_dtl1 VALUES (0001, 1012, 40);
INSERT INTO trans_dtl1 VALUES (0002, 1010, 70);
INSERT INTO trans_dtl1 VALUES (0002, 1011, 20);
INSERT INTO trans_dtl1 VALUES (0002, 1012, 80);
INSERT INTO trans_dtl1 VALUES (0003, 1010, 50);
INSERT INTO trans_dtl1 VALUES (0003, 1011, 30);
INSERT INTO trans_dtl1 VALUES (0003, 1012, 70);

--data in trans_mst2 is like
INSERT INTO trans_mst2 VALUES ('issu', 0001, TO_DATE('01-01-2007', 'dd-mm-yyyy'));
INSERT INTO trans_mst2 VALUES ('issu', 0002, TO_DATE('02-01-2007', 'dd-mm-yyyy'));
INSERT INTO trans_mst2 VALUES ('issu', 0003, TO_DATE('03-01-2007', 'dd-mm-yyyy'));

--data in trans_dtl2 is like
INSERT INTO trans_dtl2 VALUES (0001, 1010, 5 );
INSERT INTO trans_dtl2 VALUES (0001, 1011, 10);
INSERT INTO trans_dtl2 VALUES (0001, 1012, 10);
INSERT INTO trans_dtl2 VALUES (0002, 1010, 10);
INSERT INTO trans_dtl2 VALUES (0002, 1011, 10);
INSERT INTO trans_dtl2 VALUES (0002, 1012, 10);
INSERT INTO trans_dtl2 VALUES (0003, 1010, 10);
INSERT INTO trans_dtl2 VALUES (0003, 1011, 10);
INSERT INTO trans_dtl2 VALUES (0003, 1012, 10);


SELECT d1.trans_no
     , m1.doc_type
     , m1.trans_dt
     , d1.item_cd
     , d1.item_qty
FROM   trans_mst1 m1
   ,   trans_dtl1 d1
WHERE  m1.trans_no = d1.trans_no
UNION ALL
SELECT d2.trans_no
     , m2.doc_type
     , m2.trans_dt
     , d2.item_cd
     , d2.item_qty
FROM   trans_mst2 m2
   ,   trans_dtl2 d2
WHERE  m2.trans_no = d2.trans_no
ORDER BY doc_type DESC
       , trans_no
       , item_cd
       , trans_dt
/

--trans_no doc_type   trans_dt    item_cd   item_qty
--0001,    recv,      01-01-2007, 1010,     10
--0001,    recv,      01-01-2007, 1011,     20
--0001,    recv,      01-01-2007, 1012,     40
--0002,    recv,      02-01-2007, 1010,     70
--0002,    recv,      02-01-2007, 1011,     20
--0002,    recv,      02-01-2007, 1012,     80
--0003,    recv,      03-01-2007, 1010,     50
--0003,    recv,      03-01-2007, 1011,     30
--0003,    recv,      03-01-2007, 1012,     70
--0001,    issu,      01-01-2007, 1010,     5
--0001,    issu,      01-01-2007, 1011,     10
--0001,    issu,      01-01-2007, 1012,     10
--0002,    issu,      02-01-2007, 1010,     10
--0002,    issu,      02-01-2007, 1011,     10
--0002,    issu,      02-01-2007, 1012,     10
--0003,    issu,      03-01-2007, 1010,     10
--0003,    issu,      03-01-2007, 1011,     10
--0003,    issu,      03-01-2007, 1012,     10


DROP TABLE trans_mst1
/

DROP TABLE trans_mst2
/

DROP TABLE trans_dtl1
/

DROP TABLE trans_dtl2
/

When I run it, I get this:
SQL> @orafaq

Table created.

...<snip>...

1 row created.


  TRANS_NO DOC_TYPE   TRANS_DT     ITEM_CD   ITEM_QTY
---------- ---------- --------- ---------- ----------
         1 recv       01-JAN-07       1010         10
         1 recv       01-JAN-07       1011         20
         1 recv       01-JAN-07       1012         40
         2 recv       02-JAN-07       1010         70
         2 recv       02-JAN-07       1011         20
         2 recv       02-JAN-07       1012         80
         3 recv       03-JAN-07       1010         50
         3 recv       03-JAN-07       1011         30
         3 recv       03-JAN-07       1012         70
         1 issu       01-JAN-07       1010          5
         1 issu       01-JAN-07       1011         10

  TRANS_NO DOC_TYPE   TRANS_DT     ITEM_CD   ITEM_QTY
---------- ---------- --------- ---------- ----------
         1 issu       01-JAN-07       1012         10
         2 issu       02-JAN-07       1010         10
         2 issu       02-JAN-07       1011         10
         2 issu       02-JAN-07       1012         10
         3 issu       03-JAN-07       1010         10
         3 issu       03-JAN-07       1011         10
         3 issu       03-JAN-07       1012         10

18 rows selected.


Table dropped.


Table dropped.


Table dropped.


Table dropped.


MHE
Previous Topic: doubt inout procrdure
Next Topic: Not able to drop users (merged)
Goto Forum:
  


Current Time: Sun Dec 11 04:40:00 CST 2016

Total time taken to generate the page: 0.22308 seconds