Home » SQL & PL/SQL » SQL & PL/SQL » Join on two tables
Join on two tables [message #591575] |
Mon, 29 July 2013 15:04 |
|
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 |
|
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:19From your previous topic:
cookiemonster wrote on Thu, 25 July 2013 12:45Welcome 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:25cookiemonster 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 |
|
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 |
|
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 #591584 is a reply to message #591583] |
Mon, 29 July 2013 15:38 |
|
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 #591595 is a reply to message #591594] |
Tue, 30 July 2013 01:14 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Mon, 29 July 2013 22:08Many 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:19From your previous topic:
cookiemonster wrote on Thu, 25 July 2013 12:45Welcome 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:25cookiemonster 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 #591606 is a reply to message #591604] |
Tue, 30 July 2013 04:02 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
developer12 wrote on Tue, 30 July 2013 10:32I 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:14Michel Cadot wrote on Mon, 29 July 2013 22:08Many 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:19From your previous topic:
cookiemonster wrote on Thu, 25 July 2013 12:45Welcome 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:25cookiemonster 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 |
|
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 |
|
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 |
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 |
|
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 |
|
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 #591623 is a reply to message #591621] |
Tue, 30 July 2013 04:39 |
|
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 |
|
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 #594186 is a reply to message #591635] |
Sun, 25 August 2013 05:51 |
|
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.
|
|
|
Goto Forum:
Current Time: Fri Mar 29 05:00:18 CDT 2024
|