Chris,

At 05:26 PM 18/06/2004 -0700, you wrote:

>I'm trying to write a select statement that will join one portfolio of items
>with another, and retain the items that neither portfolio has in common. For
>example, if I have Portfolio 'A' with
>
>ID Value
>1 A
>2 B
>3 C
>
>And portfolio 'B' with
>
>1 A
>3 C
>4 D
>
>I'd like to get a result set like this
>
>1 A 1 A
>2 B null null
>3 C 3 C
>null null 4 D
>
>I'm using a self join on a table beginning with a left outer join, then a
>union with the same query using a right outer join, but it doesn't seem to
>work.Any suggestions, please! I'm lost and facing a deadline. Other
>approaches welcome. I'm trying to this is a stored procedure and I need to
>calculate the differences between the portfolios. Actual code that doesn't
>work is below:

[..]
Doesn't a simple (i.e. non-unioned) full join get your result?
select
h0.sec_id,
h0.HLDG_SHARES,
h0.HLDG_MKT_VALUE,
h0.HLDG_PAR_VALUE,
h.sec_id,
h.HLDG_SHARES,
h.HLDG_MKT_VALUE,
h.HLDG_PAR_VALUE,
h.HLDG_SEQNO
from holdings h0 FULL join
holdings h on h0.sec_id=h.sec_id
where h0.hsd_doc_id=40022 and
h.hsd_doc_id=42519
and h0.hldg_seqno >0
order by 1,5

/heLen