January 16, 2014 at 6:49 pm
Hi guys, new to this forum and relatively new to SQL.
I'm having trouble with an outer join.
Essentially I'm trying to retrieve all lines from 2 different tables and line them up side by side but where there is a null value in one table but not in the other, (and vice versa) I also want all these to appear.
Here's a summary of my tables:
The PORTFOLIO table contains a date (PDATE), a portfolio code (PORT) a security code (CODE) and a weight (WGT).
PDATE PORT CODE WGT
14/01/2014 ABC AU123 0.2
14/01/2014 ABC AU456 0.25
14/01/2014 ABC AU789 0.35
14/01/2014 ABC US123 0.15
14/01/2014 ABC CASH 0.05
The INDX table contains a date (IDATE) an index code (INDX) a security code (CODE) as per the PORTFOLIO table and also a weight (WGT).
IDATE INDX CODE WGT
14/01/2014 XYZ AU123 0.15
14/01/2014 XYZ AU456 0.2
14/01/2014 XYZ AU321 0.12
14/01/2014 XYZ AU246 0.17
14/01/2014 XYZ AU468 0.15
14/01/2014 XYZ AU369 0.21
The output I wish to achieve will show me the WGT for all CODE's in both tables, but with zeros or nulls when they don't exist the other table. So the sum of all WGTs should always be 1. My output should look like this:
PDATE PORT IDX CODE PWGT IWGT
14/01/2014 ABC XYZ AU123 0.2 0.15
14/01/2014 ABC XYZ AU456 0.25 0.2
14/01/2014 ABC XYZ AU789 0.35 NULL
14/01/2014 ABC XYZ US123 0.15 NULL
14/01/2014 ABC XYZ CASH 0.05 NULL
14/01/2014 ABC XYZ AU321 NULL 0.12
14/01/2014 ABC XYZ AU246 NULL 0.17
14/01/2014 ABC XYZ AU468 NULL 0.15
14/01/2014 ABC XYZ AU369 NULL 0.21
This is the code I have so far. What it does is shows me the common CODEs to both tables, ie the first 2 lines of my sample output table above. I can't seem to retrieve all the null values from both tables and have the WGT drop into the appropriate column.
select COALESCE(A.PDATE,B.IDATE) AS [DATE],
A.PORT,
B.IDX,
COALESCE(A.CODE,B.CODE) AS CODE,
A.WGT,
B.WGT
from PORTFOLIO A
full outer join INDX B
on A.PDATE = B.IDATE
AND A.CODE = B.CODE
WHERE COALESCE(A.PDATE,B.IDATE) = '2014-01-14'
Thanks for your assistance.
January 16, 2014 at 7:48 pm
Welcome to the forum! Please read the article in my signature on how to post question to the forum. Since this is your first time I did it for you 😀 create table PORTFOLIO (
PDATEDATE null
,PORTvarchar(256) null
,CODEvarchar(256) null
,WGTvarchar(256) null
);
go
create table INDX (
IDATEdate
,INDXvarchar(256) null
,CODEvarchar(256) null
,WGTvarchar(256) null
);
go
insert PORTFOLIO
values ('01/14/2014', 'ABC', 'AU123', '0.2')
,('01/14/2014', 'ABC', 'AU456', '0.25')
,('01/14/2014', 'ABC', 'AU789', '0.35')
,('01/14/2014', 'ABC', 'US123', '0.15')
,('01/14/2014','ABC', 'CASH', '0.05');
go
insert INDX
values ('01/14/2014','XYZ', 'AU123', '0.15')
,('01/14/2014','XYZ', 'AU456', '0.2')
,('01/14/2014','XYZ', 'AU321', '0.12')
,('01/14/2014','XYZ', 'AU246', '0.17')
,('01/14/2014','XYZ', 'AU468', '0.15')
,('01/14/2014','XYZ', 'AU369', '0.21');
go
With the code you provided you get the following result:
DATE PORTINDXCODE WGTWGT
2014-01-14ABCXYZAU1230.20.15
2014-01-14ABCXYZAU4560.250.2
2014-01-14ABCNULLAU7890.35NULL
2014-01-14ABCNULLUS1230.15NULL
2014-01-14ABCNULLCASH 0.05NULL
2014-01-14NULLXYZAU321NULL0.12
2014-01-14NULLXYZAU246NULL0.17
2014-01-14NULLXYZAU468NULL0.15
2014-01-14NULLXYZAU369NULL0.21
Which looks like what you want with the exception of the PORT and INDX having NULLs. Are you looking for a way to get rid of those NULLs? select COALESCE(A.PDATE,B.IDATE) AS [DATE],
ca.PORT,
ca.INDX,
COALESCE(A.CODE,B.CODE) AS CODE,
A.WGT,
B.WGT
from PORTFOLIO A
full outer join INDX B
on A.PDATE = B.IDATE
AND A.CODE = B.CODE
cross apply (select distinct PORT, INDX from PORTFOLIO, INDX) ca
WHERE COALESCE(A.PDATE,B.IDATE) = '2014-01-14' Note: this code works because you have a single PORT value and a single INDX value. If you have two different INDX values for instance you would get double the results and so on. Does that ever happen what are the rules if it does?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply