Home » SQL & PL/SQL » SQL & PL/SQL » Join on two tables
Join on two tables [message #591575] Mon, 29 July 2013 15:04 Go to next message
developer12
Messages: 88
Registered: July 2013
Member
Hi,
I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.
Now, I need to compare the data of the two tables column-wise.
So can anyone help me figure this out?? Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.
Attached are the table structures and the data it contains.
Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.
Re: Join on two tables [message #591578 is a reply to message #591575] Mon, 29 July 2013 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Remember:

Michel Cadot wrote on Mon, 29 July 2013 16:19
From your previous topic:

cookiemonster wrote on Thu, 25 July 2013 12:45
Welcome to the forum
Please read and follow How to use [code] tags and make your code easier to read?
...


dariyoosh wrote on Thu, 25 July 2013 14:09
@developer12

Please take a look at

http://www.dpriver.com/pp/sqlformat.htm
...


cookiemonster wrote on Thu, 25 July 2013 14:25
cookiemonster wrote on Thu, 25 July 2013 11:45

Please read and follow
How to use [code] tags and make your code easier to read?



Regards
Michel


And don't forget to post the result you want for the data you give and that data must contain rows that are taken and rows that are rejected.

Regards
Michel

[Updated on: Mon, 29 July 2013 15:09]

Report message to a moderator

Re: Join on two tables [message #591579 is a reply to message #591578] Mon, 29 July 2013 15:11 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
Thanks Michel.
Here it is:

-- Create table
CREATE TABLE sec_master_hista
(
security_alias NUMBER,
effective_date DATE,
coupon NUMBER,
freq NUMBER
)

/
-- Create table
CREATE TABLE sec_master_histb
(
security_alias NUMBER,
effective_date DATE,
coupon NUMBER,
freq NUMBER
)

/
-- Create table
CREATE TABLE security_mastera
(
security_alias NUMBER,
primary_asst_id VARCHAR2(100),
update_date DATE
)

/
-- Create table
CREATE TABLE security_masterb
(
security_alias NUMBER,
primary_asst_id VARCHAR2(100),
update_date DATE
)

/
INSERT INTO sec_master_hista
(security_alias,
effective_date,
coupon,
freq)
VALUES (1353,
To_date('04-07-2013', 'dd-mm-yyyy'),
77,
89);

INSERT INTO sec_master_hista
(security_alias,
effective_date,
coupon,
freq)
VALUES (98342,
To_date('06-07-2013', 'dd-mm-yyyy'),
12,
23);

INSERT INTO sec_master_hista
(security_alias,
effective_date,
coupon,
freq)
VALUES (96452,
To_date('02-07-2013', 'dd-mm-yyyy'),
11,
33);

INSERT INTO sec_master_histb
(security_alias,
effective_date,
coupon,
freq)
VALUES (6754,
To_date('06-07-2013', 'dd-mm-yyyy'),
11,
45);

INSERT INTO sec_master_histb
(security_alias,
effective_date,
coupon,
freq)
VALUES (3217,
To_date('09-07-2013', 'dd-mm-yyyy'),
5,
4);

INSERT INTO sec_master_histb
(security_alias,
effective_date,
coupon,
freq)
VALUES (23456,
To_date('10-07-2013', 'dd-mm-yyyy'),
6,
787);

INSERT INTO security_mastera
(security_alias,
primary_asst_id,
update_date)
VALUES (1353,
'HGTYR',
To_date('01-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_mastera
(security_alias,
primary_asst_id,
update_date)
VALUES (98342,
'ARRTY',
To_date('02-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_mastera
(security_alias,
primary_asst_id,
update_date)
VALUES (96452,
'YUTRU',
To_date('02-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_masterb
(security_alias,
primary_asst_id,
update_date)
VALUES (6754,
'HGTYR',
To_date('01-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_masterb
(security_alias,
primary_asst_id,
update_date)
VALUES (3217,
'ARRTY',
To_date('03-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_masterb
(security_alias,
primary_asst_id,
update_date)
VALUES (23456,
'YUTRU',
To_date('02-07-2013', 'dd-mm-yyyy'));
Re: Join on two tables [message #591581 is a reply to message #591579] Mon, 29 July 2013 15:29 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
I am sorry to print the results like this as I was not able to find another way to format it.

Security_aliasA	 Primary_AssetID_A   Security_aliasB  Primary_AssetID_B	Tablename     Fieldname SEC_MASTER_HISTA SEC_MASTER_HISTB	Comparison
    1353	      HGTYR	       6754	          HGTYR	       SEC_MASTER_HIST	Coupon	         77	         11	       Not Matching
    98342	      ARRTY	       3217	          ARRTY	       SEC_MASTER_HIST	Coupon	         12	          5	       Not Matching
    96452	      YUTRU	       23456	          YUTRU	       SEC_MASTER_HIST	Coupon	         11	          6	       Not Matching


*BlackSwan added {code} tags. Do so yourself in the future.

[Updated on: Mon, 29 July 2013 16:10] by Moderator

Report message to a moderator

Re: Join on two tables [message #591583 is a reply to message #591581] Mon, 29 July 2013 15:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/mv/msg/188850/591578/136107/#msg_591578
Re: Join on two tables [message #591584 is a reply to message #591583] Mon, 29 July 2013 15:38 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
I have also got the insert script of the result that i want:

INSERT INTO comparison
(security_aliasa,
primary_assetid_a,
security_aliasb,
primary_assetid_b,
tablename,
fieldname,
sec_master_hista,
sec_master_histb,
comparison)
VALUES (1353,
'HGTYR',
6754,
'HGTYR',
'sec_master_hist',
'coupon',
77,
11,
'not matching');

INSERT INTO comparison
(security_aliasa,
primary_assetid_a,
security_aliasb,
primary_assetid_b,
tablename,
fieldname,
sec_master_hista,
sec_master_histb,
comparison)
VALUES (98342,
'ARRTY',
3217,
'ARRTY',
'sec_master_hist',
'coupon',
12,
5,
'not matching');

INSERT INTO comparison
(security_aliasa,
primary_assetid_a,
security_aliasb,
primary_assetid_b,
tablename,
fieldname,
sec_master_hista,
sec_master_histb,
comparison)
VALUES (96452,
'YUTRU',
23456,
'YUTRU',
'sec_master_hist',
'coupon',
11,
6,
'not matching');
Re: Join on two tables [message #591585 is a reply to message #591584] Mon, 29 July 2013 16:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/591058/136107/?srch=security_alias#msg_591058
Re: Join on two tables [message #591594 is a reply to message #591575] Tue, 30 July 2013 00:59 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
@BlackSwan- sorry did not get you!
Re: Join on two tables [message #591595 is a reply to message #591594] Tue, 30 July 2013 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 29 July 2013 22:08
Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Remember:

Michel Cadot wrote on Mon, 29 July 2013 16:19
From your previous topic:

cookiemonster wrote on Thu, 25 July 2013 12:45
Welcome to the forum
Please read and follow How to use [code] tags and make your code easier to read?
...


dariyoosh wrote on Thu, 25 July 2013 14:09
@developer12

Please take a look at

http://www.dpriver.com/pp/sqlformat.htm
...


cookiemonster wrote on Thu, 25 July 2013 14:25
cookiemonster wrote on Thu, 25 July 2013 11:45

Please read and follow
How to use [code] tags and make your code easier to read?



Regards
Michel


And don't forget to post the result you want for the data you give and that data must contain rows that are taken and rows that are rejected.

Regards
Michel

Re: Join on two tables [message #591604 is a reply to message #591575] Tue, 30 July 2013 03:32 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
I have already poseted the results above as required.
Let me know if anything else is needed.
Re: Join on two tables [message #591605 is a reply to message #591604] Tue, 30 July 2013 03:42 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
developer12 wrote on Tue, 30 July 2013 10:32
... Let me know if anything else is needed ...
Just curious, is there any particular reason that you refuse to format your code by using code tags in order to provide a well indented, aligned and readable code?

Regards,
Dariyoosh
Re: Join on two tables [message #591606 is a reply to message #591604] Tue, 30 July 2013 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
developer12 wrote on Tue, 30 July 2013 10:32
I have already poseted the results above as required.
Let me know if anything else is needed.


Michel Cadot wrote on Tue, 30 July 2013 08:14
Michel Cadot wrote on Mon, 29 July 2013 22:08
Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Remember:

Michel Cadot wrote on Mon, 29 July 2013 16:19
From your previous topic:

cookiemonster wrote on Thu, 25 July 2013 12:45
Welcome to the forum
Please read and follow How to use [code] tags and make your code easier to read?

...


dariyoosh wrote on Thu, 25 July 2013 14:09
@developer12

Please take a look at

http://www.dpriver.com/pp/sqlformat.htm
...


cookiemonster wrote on Thu, 25 July 2013 14:25
cookiemonster wrote on Thu, 25 July 2013 11:45

Please read and follow
How to use [code] tags and make your code easier to read?



Regards
Michel


And don't forget to post the result you want for the data you give and that data must contain rows that are taken and rows that are rejected.

Regards
Michel


Re: Join on two tables [message #591608 is a reply to message #591606] Tue, 30 July 2013 04:11 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
I have now included the code tags also.
Please see below:


CREATE TABLE sec_master_hista
(
security_alias NUMBER,
effective_date DATE,
coupon NUMBER,
freq NUMBER
)
/
[/Create table for sec_master_hista]
[Create table for sec_master_histb]
CREATE TABLE sec_master_histb
(
security_alias NUMBER,
effective_date DATE,
coupon NUMBER,
freq NUMBER
)
/
[Create table for sec_master_histb]

CREATE TABLE security_mastera
(
security_alias NUMBER,
primary_asst_id VARCHAR2(100),
update_date DATE
)
/
[/Create table for security_mastera]

CREATE TABLE security_masterb
(
security_alias NUMBER,
primary_asst_id VARCHAR2(100),
update_date DATE
)
/
[/Create table for security_masterb]

INSERT INTO sec_master_hista
(security_alias,
effective_date,
coupon,
freq)
VALUES (1353,
To_date('04-07-2013', 'dd-mm-yyyy'),
77,
89);

INSERT INTO sec_master_hista
(security_alias,
effective_date,
coupon,
freq)
VALUES (98342,
To_date('06-07-2013', 'dd-mm-yyyy'),
12,
23);

INSERT INTO sec_master_hista
(security_alias,
effective_date,
coupon,
freq)
VALUES (96452,
To_date('02-07-2013', 'dd-mm-yyyy'),
11,
33);
[/Insert for sec_master_hista]

INSERT INTO sec_master_histb
(security_alias,
effective_date,
coupon,
freq)
VALUES (6754,
To_date('06-07-2013', 'dd-mm-yyyy'),
11,
45);

INSERT INTO sec_master_histb
(security_alias,
effective_date,
coupon,
freq)
VALUES (3217,
To_date('09-07-2013', 'dd-mm-yyyy'),
5,
4);

INSERT INTO sec_master_histb
(security_alias,
effective_date,
coupon,
freq)
VALUES (23456,
To_date('10-07-2013', 'dd-mm-yyyy'),
6,
787);
[/Insert for sec_master_histb]

INSERT INTO security_mastera
(security_alias,
primary_asst_id,
update_date)
VALUES (1353,
'HGTYR',
To_date('01-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_mastera
(security_alias,
primary_asst_id,
update_date)
VALUES (98342,
'ARRTY',
To_date('02-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_mastera
(security_alias,
primary_asst_id,
update_date)
VALUES (96452,
'YUTRU',
To_date('02-07-2013', 'dd-mm-yyyy'));
[/Insert for security_mastera]
[Insert for security_masterb]
INSERT INTO security_masterb
(security_alias,
primary_asst_id,
update_date)
VALUES (6754,
'HGTYR',
To_date('01-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_masterb
(security_alias,
primary_asst_id,
update_date)
VALUES (3217,
'ARRTY',
To_date('03-07-2013', 'dd-mm-yyyy'));

INSERT INTO security_masterb
(security_alias,
primary_asst_id,
update_date)
VALUES (23456,
'YUTRU',
To_date('02-07-2013', 'dd-mm-yyyy'));
[/Insert for security_mastera]
Re: Join on two tables [message #591609 is a reply to message #591608] Tue, 30 July 2013 04:15 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
And the results are as below:


CREATE TABLE comparison
(
security_aliasa NUMBER,
primary_assetid_a VARCHAR2(100),
security_aliasb NUMBER,
primary_assetid_b VARCHAR2(100),
tablename VARCHAR2(100),
fieldname VARCHAR2(100),
sec_master_hista VARCHAR2(100),
sec_master_histb VARCHAR2(100),
comparison VARCHAR2(100)
);
[/CREATE TABLE script -comparison ]

INSERT INTO comparison
(security_aliasa,
primary_assetid_a,
security_aliasb,
primary_assetid_b,
tablename,
fieldname,
sec_master_hista,
sec_master_histb,
comparison)
VALUES (1353,
'HGTYR',
6754,
'HGTYR',
'sec_master_hist',
'coupon',
77,
11,
'not matching');

INSERT INTO comparison
(security_aliasa,
primary_assetid_a,
security_aliasb,
primary_assetid_b,
tablename,
fieldname,
sec_master_hista,
sec_master_histb,
comparison)
VALUES (98342,
'ARRTY',
3217,
'ARRTY',
'sec_master_hist',
'coupon',
12,
5,
'not matching');

INSERT INTO comparison
(security_aliasa,
primary_assetid_a,
security_aliasb,
primary_assetid_b,
tablename,
fieldname,
sec_master_hista,
sec_master_histb,
comparison)
VALUES (96452,
'YUTRU',
23456,
'YUTRU',
'sec_master_hist',
'coupon',
11,
6,
'not matching');
[/Insert for reults table-comparison]
Re: Join on two tables [message #591610 is a reply to message #591608] Tue, 30 July 2013 04:15 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
where you have [/Insert for security_mastera] it should be [/ code ] (without the spaces) and [Insert for security_mastera] should be [ code ] (without the spaces)
Re: Join on two tables [message #591613 is a reply to message #591610] Tue, 30 July 2013 04:20 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
CREATE TABLE sec_master_hista 
( 
security_alias NUMBER, 
effective_date DATE, 
coupon NUMBER, 
freq NUMBER 
) 
/ 

CREATE TABLE sec_master_histb 
( 
security_alias NUMBER, 
effective_date DATE, 
coupon NUMBER, 
freq NUMBER 
) 
/ 

CREATE TABLE security_mastera 
( 
security_alias NUMBER, 
primary_asst_id VARCHAR2(100), 
update_date DATE 
) 
/ 

CREATE TABLE security_masterb 
( 
security_alias NUMBER, 
primary_asst_id VARCHAR2(100), 
update_date DATE 
) 
/ 

INSERT INTO sec_master_hista 
(security_alias, 
effective_date, 
coupon, 
freq) 
VALUES (1353, 
To_date('04-07-2013', 'dd-mm-yyyy'), 
77, 
89); 

INSERT INTO sec_master_hista 
(security_alias, 
effective_date, 
coupon, 
freq) 
VALUES (98342, 
To_date('06-07-2013', 'dd-mm-yyyy'), 
12, 
23); 

INSERT INTO sec_master_hista 
(security_alias, 
effective_date, 
coupon, 
freq) 
VALUES (96452, 
To_date('02-07-2013', 'dd-mm-yyyy'), 
11, 
33); 

INSERT INTO sec_master_histb 
(security_alias, 
effective_date, 
coupon, 
freq) 
VALUES (6754, 
To_date('06-07-2013', 'dd-mm-yyyy'), 
11, 
45); 

INSERT INTO sec_master_histb 
(security_alias, 
effective_date, 
coupon, 
freq) 
VALUES (3217, 
To_date('09-07-2013', 'dd-mm-yyyy'), 
5, 
4); 

INSERT INTO sec_master_histb 
(security_alias, 
effective_date, 
coupon, 
freq) 
VALUES (23456, 
To_date('10-07-2013', 'dd-mm-yyyy'), 
6, 
787); 

INSERT INTO security_mastera 
(security_alias, 
primary_asst_id, 
update_date) 
VALUES (1353, 
'HGTYR', 
To_date('01-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_mastera 
(security_alias, 
primary_asst_id, 
update_date) 
VALUES (98342, 
'ARRTY', 
To_date('02-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_mastera 
(security_alias, 
primary_asst_id, 
update_date) 
VALUES (96452, 
'YUTRU', 
To_date('02-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_masterb 
(security_alias, 
primary_asst_id, 
update_date) 
VALUES (6754, 
'HGTYR', 
To_date('01-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_masterb 
(security_alias, 
primary_asst_id, 
update_date) 
VALUES (3217, 
'ARRTY', 
To_date('03-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_masterb 
(security_alias, 
primary_asst_id, 
update_date) 
VALUES (23456, 
'YUTRU', 
To_date('02-07-2013', 'dd-mm-yyyy')); 
Re: Join on two tables [message #591614 is a reply to message #591613] Tue, 30 July 2013 04:21 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
and the results once more:

CREATE TABLE comparison 
( 
security_aliasa NUMBER, 
primary_assetid_a VARCHAR2(100), 
security_aliasb NUMBER, 
primary_assetid_b VARCHAR2(100), 
tablename VARCHAR2(100), 
fieldname VARCHAR2(100), 
sec_master_hista VARCHAR2(100), 
sec_master_histb VARCHAR2(100), 
comparison VARCHAR2(100) 
); 

INSERT INTO comparison 
(security_aliasa, 
primary_assetid_a, 
security_aliasb, 
primary_assetid_b, 
tablename, 
fieldname, 
sec_master_hista, 
sec_master_histb, 
comparison) 
VALUES (1353, 
'HGTYR', 
6754, 
'HGTYR', 
'sec_master_hist', 
'coupon', 
77, 
11, 
'not matching'); 

INSERT INTO comparison 
(security_aliasa, 
primary_assetid_a, 
security_aliasb, 
primary_assetid_b, 
tablename, 
fieldname, 
sec_master_hista, 
sec_master_histb, 
comparison) 
VALUES (98342, 
'ARRTY', 
3217, 
'ARRTY', 
'sec_master_hist', 
'coupon', 
12, 
5, 
'not matching'); 

INSERT INTO comparison 
(security_aliasa, 
primary_assetid_a, 
security_aliasb, 
primary_assetid_b, 
tablename, 
fieldname, 
sec_master_hista, 
sec_master_histb, 
comparison) 
VALUES (96452, 
'YUTRU', 
23456, 
'YUTRU', 
'sec_master_hist', 
'coupon', 
11, 
6, 
'not matching'); 
Re: Join on two tables [message #591617 is a reply to message #591614] Tue, 30 July 2013 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the end, first step is achieved.
Now indent your code.
If you don't know how to do it, learn it using SQL Formatter.

Regards
Michel
Re: Join on two tables [message #591618 is a reply to message #591617] Tue, 30 July 2013 04:31 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
I had formatted the code using the Format SQL button.
Can you guide me do I manually indent the code or is there any other option is the SQL Formatter page?
Re: Join on two tables [message #591621 is a reply to message #591618] Tue, 30 July 2013 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can indent the code using the space bar but if you don't know then use the page I pointed you to.

Regards
Michel
Re: Join on two tables [message #591623 is a reply to message #591621] Tue, 30 July 2013 04:39 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
CREATE TABLE sec_master_hista 
( 
	security_alias 	NUMBER, 
	effective_date 	DATE, 
	coupon 			NUMBER, 
	freq 			NUMBER 
) 
/

CREATE TABLE sec_master_histb 
( 
	security_alias	 	NUMBER, 
	effective_date		DATE, 
	coupon				NUMBER, 
	freq 				NUMBER 
) 
/ 

CREATE TABLE security_mastera 
( 
	security_alias 		NUMBER, 
	primary_asst_id 	VARCHAR2(100), 
	update_date 		DATE 
) 
/ 

CREATE TABLE security_masterb 
( 
	security_alias 		NUMBER, 
	primary_asst_id 	VARCHAR2(100), 
	update_date 		DATE 
) 
/ 

INSERT INTO sec_master_hista 
(	security_alias, 
	effective_date, 
	coupon, 
	freq) 
	VALUES (1353, 
	To_date('04-07-2013', 'dd-mm-yyyy'), 
	77, 
	89); 

INSERT INTO sec_master_hista 
(	security_alias, 
	effective_date, 
	coupon, 
	freq) 
	VALUES (98342, 
	To_date('06-07-2013', 'dd-mm-yyyy'), 
	12, 
	23); 

INSERT INTO sec_master_hista 
(	security_alias, 
	effective_date, 
	coupon, 
	freq) 
	VALUES (96452, 
	To_date('02-07-2013', 'dd-mm-yyyy'), 
	11, 
	33); 

INSERT INTO sec_master_histb 
(	security_alias, 
	effective_date, 
	coupon, 
	freq) 
	VALUES (6754, 
	To_date('06-07-2013', 'dd-mm-yyyy'), 
	11, 
	45); 

INSERT INTO sec_master_histb 
(	security_alias, 
	effective_date, 
	coupon, 
	freq) 
	VALUES (3217, 
	To_date('09-07-2013', 'dd-mm-yyyy'), 
	5, 
	4); 

INSERT INTO sec_master_histb 
(	security_alias, 
	effective_date, 
	coupon, 
	freq) 
	VALUES (23456, 
	To_date('10-07-2013', 'dd-mm-yyyy'), 
	6, 
	787); 

INSERT INTO security_mastera 
(	security_alias, 
	primary_asst_id, 
	update_date) 
	VALUES (1353, 
	'HGTYR', 
	To_date('01-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_mastera 
(	security_alias, 
	primary_asst_id, 
	update_date) 
	VALUES (98342, 
	'ARRTY', 
	To_date('02-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_mastera 
(	security_alias, 
	primary_asst_id, 
	update_date) 
	VALUES (96452, 
	'YUTRU', 
	To_date('02-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_masterb 
(	security_alias, 
	primary_asst_id, 
	update_date) 
	VALUES (6754, 
	'HGTYR', 
	To_date('01-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_masterb 
(	security_alias, 
	primary_asst_id, 
	update_date) 
	VALUES (3217, 
	'ARRTY', 
	To_date('03-07-2013', 'dd-mm-yyyy')); 

INSERT INTO security_masterb 
(	security_alias, 
	primary_asst_id, 
	update_date) 
	VALUES (23456, 
	'YUTRU', 
	To_date('02-07-2013', 'dd-mm-yyyy')); 
Re: Join on two tables [message #591625 is a reply to message #591623] Tue, 30 July 2013 04:45 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
CREATE TABLE comparison 
( 
	security_aliasa 	NUMBER, 
	primary_assetid_a 	VARCHAR2(100), 
	security_aliasb 	NUMBER, 
	primary_assetid_b 	VARCHAR2(100), 
	tablename 			VARCHAR2(100), 
	fieldname 			VARCHAR2(100), 
	sec_master_hista 	VARCHAR2(100), 
	sec_master_histb 	VARCHAR2(100), 
	comparison 			VARCHAR2(100) 
); 

INSERT INTO comparison 
(	security_aliasa, 
	primary_assetid_a, 
	security_aliasb, 
	primary_assetid_b, 
	tablename, 
	fieldname, 
	sec_master_hista, 
	sec_master_histb, 
	comparison) 
	VALUES (1353, 
	'HGTYR', 
	6754, 
	'HGTYR', 
	'sec_master_hist', 
	'coupon', 
	77, 
	11, 
	'not matching'); 

INSERT INTO comparison 
(	security_aliasa, 
	primary_assetid_a, 
	security_aliasb, 
	primary_assetid_b, 
	tablename, 
	fieldname, 
	sec_master_hista, 
	sec_master_histb, 
	comparison) 
	VALUES (98342, 
	'ARRTY', 
	3217, 
	'ARRTY', 
	'sec_master_hist', 
	'coupon', 
	12, 
	5, 
	'not matching'); 

INSERT INTO comparison 
(	security_aliasa, 
	primary_assetid_a, 
	security_aliasb, 
	primary_assetid_b, 
	tablename, 
	fieldname, 
	sec_master_hista, 
	sec_master_histb, 
	comparison) 
	VALUES (96452, 
	'YUTRU', 
	23456, 
	'YUTRU', 
	'sec_master_hist', 
	'coupon', 
	11, 
	6, 
	'not matching'); 
Re: Join on two tables [message #591635 is a reply to message #591575] Tue, 30 July 2013 07:32 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
Can anyone help?? Confused Confused
Re: Join on two tables [message #594186 is a reply to message #591635] Sun, 25 August 2013 05:51 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- test data:
SCOTT@orcl12c> select * from sec_master_hista
  2  /

SECURITY_ALIAS EFFECTIVE_DATE      COUPON       FREQ
-------------- --------------- ---------- ----------
          1353 Thu 04-Jul-2013         77         89
         98342 Sat 06-Jul-2013         12         23
         96452 Tue 02-Jul-2013         11         33

3 rows selected.

SCOTT@orcl12c> select * from sec_master_histb
  2  /

SECURITY_ALIAS EFFECTIVE_DATE      COUPON       FREQ
-------------- --------------- ---------- ----------
          6754 Sat 06-Jul-2013         11         45
          3217 Tue 09-Jul-2013          5          4
         23456 Wed 10-Jul-2013          6        787

3 rows selected.

SCOTT@orcl12c> column primary_asst_id format a15
SCOTT@orcl12c> select * from security_mastera
  2  /

SECURITY_ALIAS PRIMARY_ASST_ID UPDATE_DATE
-------------- --------------- ---------------
          1353 HGTYR           Mon 01-Jul-2013
         98342 ARRTY           Tue 02-Jul-2013
         96452 YUTRU           Tue 02-Jul-2013

3 rows selected.

SCOTT@orcl12c> select * from security_masterb
  2  /

SECURITY_ALIAS PRIMARY_ASST_ID UPDATE_DATE
-------------- --------------- ---------------
          6754 HGTYR           Mon 01-Jul-2013
          3217 ARRTY           Wed 03-Jul-2013
         23456 YUTRU           Tue 02-Jul-2013

3 rows selected.


-- query:
SCOTT@orcl12c> column primary_assetid_a format a17
SCOTT@orcl12c> column primary_assetid_b format a17
SCOTT@orcl12c> column tablename 	format a15
SCOTT@orcl12c> column fieldname 	format a9
SCOTT@orcl12c> column comparison	format a12
SCOTT@orcl12c> select sma.security_alias  as security_aliasa,
  2  	    sma.primary_asst_id as primary_assetid_a,
  3  	    smb.security_alias	as security_aliasb,
  4  	    smb.primary_asst_id as primary_assetid_b,
  5  	    'sec_master_hist'	as tablename,
  6  	    'coupon'		as fieldname,
  7  	    smha.coupon 	as sec_master_hista,
  8  	    smhb.coupon 	as sec_master_histb,
  9  	    'Not Matching'	as comparison
 10  from   security_mastera sma,
 11  	    security_masterb smb,
 12  	    sec_master_hista smha,
 13  	    sec_master_histb smhb
 14  where  sma.primary_asst_id = smb.primary_asst_id
 15  and    sma.security_alias = smha.security_alias
 16  and    smb.security_alias = smhb.security_alias
 17  and    smha.coupon != smhb.coupon
 18  union all
 19  select sma.security_alias	as security_aliasa,
 20  	    sma.primary_asst_id as primary_assetid_a,
 21  	    smb.security_alias	as security_aliasb,
 22  	    smb.primary_asst_id as primary_assetid_b,
 23  	    'sec_master_hist'	as tablename,
 24  	    'freq'		as fieldname,
 25  	    smha.freq		as sec_master_hista,
 26  	    smhb.freq		as sec_master_histb,
 27  	    'Not Matching'	as comparison
 28  from   security_mastera sma,
 29  	    security_masterb smb,
 30  	    sec_master_hista smha,
 31  	    sec_master_histb smhb
 32  where  sma.primary_asst_id = smb.primary_asst_id
 33  and    sma.security_alias = smha.security_alias
 34  and    smb.security_alias = smhb.security_alias
 35  and    smha.freq != smhb.freq
 36  /

SECURITY_ALIASA PRIMARY_ASSETID_A SECURITY_ALIASB PRIMARY_ASSETID_B TABLENAME       FIELDNAME SEC_MASTER_HISTA SEC_MASTER_HISTB COMPARISON
--------------- ----------------- --------------- ----------------- --------------- --------- ---------------- ---------------- ------------
           1353 HGTYR                        6754 HGTYR             sec_master_hist coupon                  77               11 Not Matching
          98342 ARRTY                        3217 ARRTY             sec_master_hist coupon                  12                5 Not Matching
          96452 YUTRU                       23456 YUTRU             sec_master_hist coupon                  11                6 Not Matching
           1353 HGTYR                        6754 HGTYR             sec_master_hist freq                    89               45 Not Matching
          98342 ARRTY                        3217 ARRTY             sec_master_hist freq                    23                4 Not Matching
          96452 YUTRU                       23456 YUTRU             sec_master_hist freq                    33              787 Not Matching

6 rows selected.

Previous Topic: How to make IS NOT NULL condition to use index
Next Topic: dynamic sql
Goto Forum:
  


Current Time: Fri Mar 29 05:00:18 CDT 2024